Re: Joining on rownum problem

As far as I can tell, RowNum in a query or subquery refers to the row number *within that query*, not its overall position in the table. So,
select RowNum from test where two = 'two' will get you
ROWNUM

    1
    2
    3
    4

not 3, 4, 5, 6, like you were expecting. Thus, your row number in your subquery only maps to the row number in the overall table for the first row - which is why it's the only one that gets returned.

Maybe RowID would work better?

• Catherine

In article <4ZFz6.1084$ix4.657070_at_news1.rdc1.sfba.home.com>, Mike Moore <hicamel_at_mail.home.com> writes:
>SQL> select two,one,four from test;
>
>TWO ONE FOUR
>-------------------- ---------- ----------------------------------------
>is this data
>fun more dick and jane
>two one four
>two most yowsa
>two bosco yowsa
>two zabba yowsa
>
>6 rows selected.
>
>SQL> get junk1
> 1 select a.two, a.one, a.four from test a
> 2* where rownum = (select max(rownum) from test b where a.two = b.two)
>SQL> /
>
>TWO ONE FOUR
>-------------------- ---------- ----------------------------------------
>is this data
>
>
>I expected ....
>
>is this data
>fun more dick and jane
>two zabba yowsa
>
>
>This technique seems to get what I want if I use another column of the
>table instead of rownum.
>Why does it stop prematurely?
>thanks
>Mike
>
>
>
>
>

• Posted via NewsOne.Net: Free (anonymous) Usenet News via the Web ----- http://newsone.net/ -- Free reading and anonymous posting to 60,000+ groups NewsOne.Net prohibits users from posting spam. If this or other posts made through NewsOne.Net violate posting guidelines, email abuse_at_newsone.net