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;

 

2 Responses to “Rownum & Order by”

  1. Kurt Says:

    Still I get the error: ORA-00907: missing right parenthesis

    SELECT * FROM (SELECT * FROM History ORDER BY Start) WHERE ROWNUM<=5;

    Oracle SQL Plus 9.2.0.1.0 marked ORDER BY with a Star “*”.

    I’m going crazy…!!!

    Microsoft SQL Server is so much better!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s