rownum

rownum in Subquery

I was wondring why this doesn't work: SELECT ( SELECT COALESCE(MAX(a.LaufNr),0) + TBL.Rownum FROM schemaB.PersonRelatedTbl A WHERE A.Persid = Tbl.Persid ) , Tbl.Some_Other_Attributs FROM schemaA.PersonRelatedTbl TBL WHERE ... This + TBL.Rownum gives an error why? greets Auro ...

Oracle 10G - Query using rownum stopped working after migration from 9i

Hi, We just recently moved our DB from 9i to 10G (Yes..better late than never and No - moving to 11g is currently not an option :-)) Details of my Oracle 10G DB are :- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod PL/SQL Release 10.2.0.1.0 - Production CORE 10.2.0.1.0 Production I am faced with a very wei...

Sql query on rownum

SELECT instmax FROM (SELECT instmax ,rownum r FROM ( SELECT instmax FROM pswlinstmax ORDER BY instmax DESC NULLS LAST ) WHERE r = 2 ); After execution it's giving this error: ORA-00904: "R": invalid identifier 00904. 00000 - "%s: invalid identifier" *Cause: *Action: Error at Line: 39 Column: 8 why it's giving th...

why both the sql query on rownum are giving different result

>SELECT instmax FROM (SELECT instmax, rownum r FROM ( SELECT * FROM pswlinstmax ORDER BY instmax DESC NULLS LAST ) ) WHERE r = 2; INSTMAX ------- 1049 >SELECT instmax FROM (SELECT instmax, rownum FROM (SELECT * FROM pswlinstmax ORDER BY instmax DESC ) ) WHERE...

why there's a difference of performance gain among both these queries?

SELECT instmax, r FROM (SELECT instmax, rownum r FROM ( SELECT instmax FROM pswlinstmax ORDER BY instmax DESC NULLS LAST ) WHERE rownum <= 10 ) WHERE r >=6; Output SELECT instmax, r FROM (SELECT instmax, rownum r FROM ( SELECT instmax FROM psw...

How can I return multiple identical rows based on a quantity field in the row itself?

I'm using oracle to output line items in from a shopping app. Each item has a quantity field that may be greater than 1 and if it is, I'd like to return that row N times. Here's what I'm talking about for a table product_id, quanity 1, 3, 2, 5 And I'm looking a query that would return 1,3 1,3 1,3 2,5 2,5 2,5 2,5 2,5 Is this possi...

Rownum in postgresql

Is there any way to simulate rownum in postgresql ? ...