Tutorial :Equivalents to SQL Server TOP



Question:

In SQL Server, TOP may be used to return the first n number of rows in a query. For example,

SELECT TOP 100 * FROM users ORDER BY id
might be used to return the first 100 people that registered for a site. (This is not necessarily the best way, I am just using it as an example).

My question is - What is the equivalent to TOP in other databases, such as Oracle, MySQL, PostgreSQL, etc? If there is not an equivalent keyword, what workarounds can you recommend to achieve the same result?


Solution:1

To select first 100 rows:

MySQL and PostgreSQL:

SELECT  *  FROM    Table  ORDER BY          column  LIMIT 100  

Oracle:

SELECT  *  FROM    (          SELECT  t.*          FROM    table          ORDER BY                  column          )  WHERE   rownum <= 100  

Note that you need a subquery here. If you don't add a subquery, ROWNUM will select first 10 rows in random order and then sort them by column.

To select rows between 100 and 300:

MySQL:

SELECT  *  FROM    TABLE  ORDER BY          column  LIMIT   100, 200  

PostgreSQL:

SELECT  *  FROM    Table  ORDER BY          column  OFFSET 100 LIMIT 200  

Oracle:

SELECT  *  FROM    (          SELECT  t.*, ROW_NUMBER() OVER (ORER BY column) AS rn          FROM    table          )  WHERE   rn >= 100          AND rownum <= 200  

Note that an attempt to simplify it with ROWNUM BETWEEN 100 AND 200 (as opposed to rn BETWEEN 100 AND 200 in the outer query) will return nothing in Oracle!

RN BETWEEN 100 AND 200 will work in Oracle too but is less efficient.

See the article in my blog for performance details:


Solution:2

For Postgres and MySQL it's the LIMIT keyword.

SELECT *    FROM users   ORDER BY id   LIMIT 100;  


Solution:3

This is standard SQL (Oracle and SQL Server implement it). This is an example of returning up to 100 rows:

        SELECT ID_CONTROL FROM (SELECT ROW_NUMBER() OVER (ORDER BY ID_CONTROL)           ROWNUMBER, ID_CONTROL FROM IWS_CONTROL WHERE           CURRENT_STATE = 15 AND CURRENT_STATUS=0) A WHERE ROWNUMBER <= 100)  


Solution:4

In SQL Anywhere, it's the same as SQL Server:

SELECT TOP 100 * FROM users ORDER BY id  

You can even start in the middle of the result set if you want:

SELECT TOP 100 START AT 50 * FROM users ORDER BY id  

gets the 50th through 150th rows of the result set.


Solution:5

LIMIT 100  

as in

SELECT * FROM foo ORDER BY bar LIMIT 100  


Solution:6

You can use RANK() and DENSE_RANK() in Oracle. Here is a link to AskTom website explaining how to to pagination and top-n queries with DENSE_RANK in Oracle.


Solution:7

Oracle:

select * from (select * from foo ORDER BY bar) where rownum < 100  

With a nice explanation on how to make it work in AskTom.

In Ingres the same query would by:

select First 100 * from foo ORDER BY bar  

Ingres question was already answered in StackOverflow before.


Solution:8

In DB2 you would make your query look like this:

SELECT * FROM tblData FETCH FIRST 10 ROWS ONLY;


Solution:9

In Oracle you want to use a TOP-N query.

For example:

select  *    from  (SELECT  *             FROM  foo            where  foo_id=[number]         order by  foo_id desc)   where  rownum <= 3  

This will get you the top three results (because I order by desc in the sub query)


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »