Re: view with a rownum

This one works.

CREATE VIEW view3 AS SELECT
p.file_name,r.num_total,r.num_detected,r.num_false,count(rs.result_id) AS rid FROM pictures p, results r,results rs WHERE

r.picture_id=p.picture_id AND r.version_id=2 AND 
r.result_id>rs.result_id AND rs.version_id=r.version_id GROUP BY 
r.result_id,p.file_name,r.num_total,r.num_detected,r.num_false;"

It seems to like to add a clause r.version_id=rs.version_id if you have r.version_id=2
and count the alias count(rs.result_id)

Thank you for your help

Lale
Andy Spaven wrote:

>Try joining rs to p just like you have joined r - with picture_id.
>
>-----Original Message-----
>From: Ilariu Raducan [mailto:lale_at_fotonation.com]
>Sent: 30 May 2003 13:23
>To: Andy Spaven
>Subject: Re: view with a rownum
>
>
>Almost there:
>
> >>> cur.execute("DROP TABLE view3;")
> >>> q = "CREATE VIEW view3 AS SELECT
>p.file_name,r.num_total,r.num_detected,r.num_false,count(r.result_id) AS
>rid FROM pictures p, results r,results rs WHERE
>r.picture_id=p.picture_id AND r.version_id=2 AND
>r.result_id>=rs.result_id GROUP BY
>r.result_id,p.file_name,r.num_total,r.num_detected,r.num_false;"
> >>> cur.execute(q)
> >>> qs = "SELECT * FROM view3;"
> >>> cur.execute(qs)
> >>> rows=cur.fetchall()
> >>> pprint(rows)
>((u'DCP_4809.JPG', 3, 2, 0, 18),
> (u'DCP_4810.JPG', 2, 0, 2, 19),
> (u'DCP_4811.JPG', 2, 2, 2, 20),
> (u'DCP_4813.JPG', 2, 2, 2, 21),
> (u'DCP_4818.JPG', 0, 0, 0, 22),
> (u'DCP_4819.JPG', 2, 1, 1, 23),
> (u'DCP_4820.JPG', 2, 0, 0, 24),
> (u'DCP_4821.JPG', 2, 0, 0, 25),
> (u'DCP_4822.JPG', 3, 0, 1, 26),
> (u'DCP_4823.JPG', 1, 0, 0, 27),
> (u'DCP_4831.JPG', 2, 0, 0, 28),
> (u'DCP_4834.JPG', 2, 0, 0, 29),
> (u'DCP_4838.JPG', 1, 1, 0, 30),
> (u'DCP_4839.JPG', 2, 2, 0, 31),
> (u'DCP_4840.JPG', 4, 0, 0, 32),
> (u'DCP_4841.JPG', 3, 0, 0, 33),
> (u'DCP_4843.JPG', 2, 2, 0, 34),
> (u'DCP_4808.JPG', 6, 0, 0, 53))
>
>But something still missing
>
>Lale
>
>Andy Spaven wrote:
>
>
>
>>Aaah, thought you wanted it for Oracle outside of the join - MS Jet engine
>>is a bit poor on the inline view [SELECT FROM (SELECT...)...] and
>>SQL-89/SQL-92 standards full stop. The other alternative is to join two
>>instances of the result set together and count how many in the second set
>>fall before the record in the first set to get a pseudo rownum.
>>
>>select a1.col1, a1.col2, count(*) from tableA a1, tableA a2
>>where a1.key >= a2.key
>>group by a1.col1, a1.col2
>>
>>The problems come when the key is more than one column or if the "key"
>>column is not really a key and is non-unique.
>>
>>Adapting the above to your query of two tables might require that tableA is
>>actually translated into an inline view.
>>
>>Hope this helps
>>Andy
>>
>>-----Original Message-----
>>From: Ilariu Raducan [mailto:lale_at_fotonation.com]
>>Sent: 30 May 2003 11:18
>>To: Andy
>>Subject: Re: view with a rownum
>>
>>
>>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 - 14:55:22 CEST