Re: view with a rownum

Try

CREATE VIEW view3 AS
SELECT *, rownum FROM ( your select without rownumber but with join )

The rownum is then the rownum of the record from the sub-query.

It's a technique worth remembering as you can also use this technique to get rownum for an ordered data set - the subquery can be ordered, the rownum being "added" to the final dataset after the ordering rather than before as is normally the case.

Andy

"Ilariu Raducan" <lale_at_fotonation.com> wrote in message news:JDrBa.15762$pK2.21592_at_news.indigo.ie...
> Hi All,
>
> I'm trying to create a view from two tables that will have a column
> rownumber autoincrement.
> Don't laugh, all that in Microsoft Jet Engine.
>
> CREATE VIEW view3 AS
> SELECT p.file_name,r.num_total,r.num_detected,r.num_false,rownumber
> FROM pictures p, results r WHERE r.picture_id=p.picture_id AND
> r.version_id=2;
>
> Is it possible to do that?
>
> Thank you,
> Lale
>
Received on Fri May 30 2003 - 04:20:56 CDT