Re: Joining on rownum problem

rownum is just the number of the row that comes back from the result set before any sorting. It is not the actual number of the row in the database. So the following would not return any rows: select * from test where rownum>2;

Why? Because rownum is the number of the row in the result set and in the above query you are limiting the result set by itself. Rownum is arbitrary. Jim

"Mike Moore" <hicamel_at_mail.home.com> wrote in message news:4ZFz6.1084$ix4.657070_at_news1.rdc1.sfba.home.com...
> 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
>
>
>
>
>
Received on Sat Apr 07 2001 - 18:22:04 CEST