Re: view with a rownum

From: Ilariu Raducan <lale_at_fotonation.com>
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

Original text of this message