Sorry -- I don't know! I don't see much value in "rownum()" either. I've never needed or wanted to use it for anything.
On Fri, May 14, 2010 at 9:05 PM, Andrew Pushkin <[email protected]> wrote: > Thanks Kerry for looking into this. > > However I still cannot get why if the rownum() is specified in the inner > query, it is still applied to the outer query? Is there any rationale behind > this? > > Thanks! > > > On 14 May 2010 01:51, Kerry Sainsbury <[email protected]> wrote: > >> Yes, that's the correct behaviour. If your query returns 1 row it will >> have a rownum of 1. >> >> rownums are not persistent -- they relate to the results your query has >> just returned. They are NOT an internal identifier, like "rowid"s are in >> Informix databases (for example) >> >> Cheers >> Kerry >> >> On Fri, May 14, 2010 at 4:02 AM, Andrew Pushkin <[email protected]>wrote: >> >>> Hi All, >>> >>> I have a question about "rownum" function work. >>> >>> I have table "Keyword" >>> >>> create table Keyword ( >>> id bigint generated by default as identity, >>> text varchar(255) not null, >>> project_id bigint not null, >>> primary key (id), >>> unique (text, project_id) >>> >>> ); >>> >>> id | text | project_id >>> ---+------+----------- >>> 1 | t1 | 8 >>> 2 | t6 | 4 >>> 3 | t2 | 6 >>> 4 | t9 | 2 >>> 5 | t0 | 1 >>> 6 | t4 | 7 >>> 7 | t3 | 2 >>> >>> for sql select >>> select * from KEYWORD where project_id = 2 >>> >>> I have >>> id | text | project_id >>> ---+------+----------- >>> 4 | t9 | 2 >>> 7 | t3 | 2 >>> >>> for sql select >>> select rownum,* from KEYWORD where project_id = 2 >>> >>> I have >>> ROWNUM() | id | text | project_id >>> ---------+----+------+----------- >>> 1 | 4 | t9 | 2 >>> 2 | 7 | t3 | 2 >>> >>> >>> for sql select >>> select * from (select rownum,* from KEYWORD where project_id = 2) where >>> id=7 >>> >>> I have >>> ROWNUM() | id | text | project_id >>> ---------+----+------+----------- >>> 1 | 7 | t3 | 2 >>> >>> But I expect to get >>> ROWNUM() | id | text | project_id >>> ---------+----+------+----------- >>> 2 | 7 | t3 | 2 >>> >>> ROWNUM() = 1 instead of 2 >>> >>> Is it right behaviour? >>> >>> Thanks in advance, >>> Andrew >>> >>> >>> -- >>> You received this message because you are subscribed to the Google Groups >>> "H2 Database" group. >>> To post to this group, send email to [email protected]. >>> To unsubscribe from this group, send email to >>> [email protected]<h2-database%[email protected]> >>> . >>> For more options, visit this group at >>> http://groups.google.com/group/h2-database?hl=en. >>> >> >> -- >> You received this message because you are subscribed to the Google Groups >> "H2 Database" group. >> To post to this group, send email to [email protected]. >> To unsubscribe from this group, send email to >> [email protected]<h2-database%[email protected]> >> . >> For more options, visit this group at >> http://groups.google.com/group/h2-database?hl=en. >> > > -- > You received this message because you are subscribed to the Google Groups > "H2 Database" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]<h2-database%[email protected]> > . > For more options, visit this group at > http://groups.google.com/group/h2-database?hl=en. > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
