--------------->
30th Sep, 2008

How do I limit number of rows returned in SQL Server, Oracle and MySQL

I am currently working on all 3 major databases now:  SQL Server and Oracle for my 9 to 5 job; MySQL for personal use.  One day one of my friends asked me, “How do you limit row count to only show the first 10 rows in Oracle?”  And I quickly answered him “Oh… add limit 10 at the end”.  Right after I said that, I realized that it does not work for Oracle because it is a MySQL syntax!

Here is a table for quick comparison for the syntax difference for SQL Server, Oracle and MySQL.  All of these examples do the exact same thing: return 10 rows from a result set.

Database Last 10 login User (sort by login date and limit to 10 row return)
SQL Server select TOP 10 * from users order by lastlogin desc
Oracle select * from users WHERE ROWNUM<=10 order by lastlogin desc
MySQL select * from users order by lastlogin desc LIMIT 10

I personally like MySQL syntax the best! It is easy to understand and  easy to remember and ofcourse makes total sense!

Share and Enjoy: These icons link to social bookmarking sites where readers can share and discover new web pages.
  • Digg
  • del.icio.us
  • De.lirio.us

Posted by: tracy

Categories:

Responses

Hi Tracy,

Just a little error in the Oracle query because Oracle will process the rownum condition before the order by. So the output will not necessarily gives the 10 you are looking for.

The query should be:
select * from (
select * from users order by lastlogin desc)
WHERE ROWNUM<=10;

This will ensure the order by clause is done first and then the top 10.

By: Stephane Champoussin on October 12th, 2009
at 9:08 am

Leave a response






Your response:

Categories