Re: Why does SELECT not get expected results?

Rownum doesn't always generate the results you might expect. For example, if you have a selection which returns 20 rows, and your where clause is "where rownum > 9" you won't get 10 rows, you will get none. This is because every number of the result set gets numbered with a rownum. So the first row gets checked, and its rownum = 1, which is less than 9, so it doesn't get selected. Then the second row gets checked, and its rownum would also be 1 as there are no rows in the result set yet, and so on for the rest of the rows.

Also, in your SQL you have two selects, and the rownums of them will be different - the rownum in your nested select may in no way reflect the rownum of your main select. You just can't use rownum in the way you are trying to.

Try using a group by instead.

Christopher Latta

Mike Moore <hicamel_at_mail.home.com> wrote in message news:HXaz6.6349$4L4.663933_at_news1.frmt1.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 Thu Apr 05 2001 - 22:56:46 CDT