Karatas, FERHAT
I would love to change the world, but they won't give me the source code!
SQL SELECT TOP N equivalent in ORACLE
4.09.2006 01:38:58 - Filed under : Oracle
SQL Server:
SELECT TOP 10 product, descr, email FROM products

Oracle:
SELECT product, descr, email FROM products WHERE ROWNUM <= 10
Actually the code for Oracle only works if you do not ORDER BY. Let's say you have lots of records and you want to sort them by "product" and getting just the top 10 records. You can't do it by just:
"SELECT product, descr, email FROM products WHERE ROWNUM <= 10 ORDER BY product" The result is not what you wanted.

What "ROWNUM <= 10" does is simply return you top 10 record in the table.. if you try the ORDER BY code, it will simply extract the top 10 records in the table then ORDER it instead of ORDERING them then select the top 10.

Doesnt work :
SELECT product, descr, email FROM products WHERE ROWNUM <= 10 ORDER BY product

It works :
SELECT product, descr, email FROM (select product, descr, email from products order by product )
WHERE ROWNUM <= 10

Related Terminology : Select TOP and Order By statement in Oracle
with 0 comments

What do you think ?
Security Code 523
Check S. Code
Name  
Email  
Home Page  
Comments