Akdora’s Blog

Programming, Oracle, Life, Fun

Rownum & Order by September 26, 2007

Filed under: Non-technical — Akdora @ 8:27 am
Tags: , , , ,

Warning,

If you user rownum like this,

SELECT *

FROM TABLE

WHERE rownum < 2

ORDER BY column

you do this : get the first row that readed from database, and sort the 1 row.

Correct :

SELECT *

FROM (SELECT *

   FROM TABLE

   ORDER BY COLUMN

   )

WHERE rownum < 2

That means : sort the rows and get the first row.

 

For paging:

SELECT ID  FROM (SELECT ID, ROWNUM AS RNUM FROM (SELECT ID FROM ROWNUM_ORDER_TEST ORDER BY ID) WHERE ROWNUM <= 8) WHERE RNUM >= 4;

Advertisements