Error in SQL Tutorial - SQL SELECT TOP Clause

[vdeconinck]

Hi,

 

 

That tutorial says that the SQL SELECT TOP equivalent in Oracle is "where rownum <= number".

 

This is not the case, because as soon as you are using an "order by" clause (and most of the time, selecting top rows without sorting them first is meaningless), the returned rows are not the top ones, they are pseudo-randomly selected and the "order by" is only applied after that selection has occurred.

 

After a bit of reading, the correct Oracle equivalent to :

SELECT TOP number column_name(s)FROM table_nameORDER BY order_clause;

is not :

SELECT column_name(s)FROM table_nameWHERE ROWNUM <= numberORDER BY order_clause;

it a nested query of the form :

SELECT * FROM (  SELECT column_name(s)  FROM table_name  ORDER BY order_clause) WHERE ROWNUM <= number;

I guess that it should be indicated because it's a real trap that can go unnoticed. I know it, because I fell into it :-)

 

Best regards,

 

 

Vincent

 

 

PS: Note that the "limit" syntax in MySQL does not have that issue

 

Edited April 30, 2014 by vdeconinck