Tip Of The Day: T-SQL "TOP N" equivalent for Oracle and MySQL
You know that T-SQL (SQL Server) has a nice keyword Top N to fetch the top N number of rows. Great…so what is the equivalent of it in Oracle and MySQL. Here it is,
SQL Server:
SELECT TOP 10 product, descr, email
FROM products
ORACLE:
SELECT product, descr, email
FROM products
WHERE ROWNUM <= 10
MySQL:
SELECT product, descr, email
FROM products
LIMIT 10
Visit DemoGeek.com for amazingly detailed quality articles on Computer, Internet, Browsers, Software, Programming and much more.
~
WinDev - Everything Windows Development 
Be careful with the Oracle syntax. As posted it will work, but if an ORDER BY clause is added, you will get unexpected results, because the “Order By” is applied following the WHERE condition.
For Oracle, to get expected results with an order by clause, you will need to do a sub select as follows.
Select product, descr, email
FROM
(
SELECT product, descr, email
FROM products
order by product
)
WHERE ROWNUM
Duane McGuire
January 27, 2007
Be careful with the Oracle syntax. As posted it will work, but if an ORDER BY clause is added, you will get unexpected results, because the “Order By” is applied following the WHERE condition.
For Oracle, to get expected results with an order by clause, you will need to do a sub select as follows.
Select product, descr, email
FROM
(
SELECT product, descr, email
FROM products
order by product
)
WHERE ROWNUM <= 10
The sqlserver syntax is much simpler, and more intuitive with an order by clause:
SELECT TOP 10 product, descr, email
FROM products
ORDER BY product
Duane McGuire
January 27, 2007
Thank you Duane for clarifying that. Indeed the sample that I’ve posted was to demonstrate the concept that these are the syntactic means to get the TOP N rows from the result set.
Adding an ORDER BY to the sample, yes surely the clarification you’ve made helps. Thanks again.
askars
January 27, 2007
popular free ringtones
http://www.thehotstop.info
signature…
freeeeringtones
August 18, 2007
Hello webmaster
I would like to share with you a link to your site
write me here preonrelt@mail.ru
Alexwebmaster
March 3, 2009
Note that this won’t work well in Oracle if you do sort – since the rownum condition is applied before the sort. So – you need to do
select * from
()
where rownum < X
Rami Jaschek
September 12, 2011