Tip Of The Day: T-SQL "TOP N" equivalent for Oracle and MySQL

Posted on January 17, 2007. Filed under: Code Samples, Database, Tips and Tricks |

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.

Technorati tags: , , , , ,

~

About these ads

Make a Comment

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

6 Responses to “Tip Of The Day: T-SQL "TOP N" equivalent for Oracle and MySQL”

RSS Feed for WinDev – Everything Windows Development Comments RSS Feed

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

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

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.

popular free ringtones

http://www.thehotstop.info

signature…

Hello webmaster
I would like to share with you a link to your site
write me here preonrelt@mail.ru

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


Where's The Comment Form?

  • January 2007
    M T W T F S S
    « Dec   Feb »
    1234567
    891011121314
    15161718192021
    22232425262728
    293031  
  • Del.icio.us

  • Subscribe

    Subscribe in NewsGator Online

    Subscribe in Rojo

    Add WinDev to Newsburst from CNET News.com

    Add to My AOL

    Add to netvibes

    Subscribe in Bloglines

    Subscribe in NewsAlloy

    Add WinDev to ODEO

    Subscribe in podnova

    Add to Pageflakes

Liked it here?
Why not try sites on the blogroll...

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: