Monday, February 25, 2008

Get Fun with ROWNUM...

I have data on test table.
SQL> desc test
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER

SQL> select id from test;

ID
----------
1
2
3
.
.
.
68603
68604
68605

68605 rows selected

I need to get the third max (68603) from test table.
I don't know any solution else. So....
SQL> select * from (select * from test order by id desc) where rownum <=3 ;
ID
----------
68605
68604
68603

SQL> select * from (select * from test order by id desc) where rownum <=3 order by id;

ID
----------
68603
68604
68605

SQL> select * from ( select * from (select * from test order by id desc) where rownum <=3 order by id) where rownum <=1;

ID
----------
68603

Enjoy!

No comments: