Re: view with a rownum
Date: Fri, 30 May 2003 11:17:47 +0100
Message-ID: <3ED72FCB.6050509_at_fotonation.com>
That will work for Oracle.
In MS Jet Engine(used by Microsoft Access) rownum is not defined
My query evolved to:
CREATE VIEW view3 AS SELECT
p.file_name,r.num_total,r.num_detected,r.num_false,count(*) AS rid FROM
pictures p, results r WHERE r.picture_id=p.picture_id AND r.version_id=2
GROUP BY r.result_id,p.file_name,r.num_total,r.num_detected,r.num_false;"
But rid is now 1 for all rows.
Something is missing.
Lale
Andy wrote:
> 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 - 12:17:47 CEST