Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Equivalents to SQL Server TOP

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?

like image 997
Justin Ethier Avatar asked May 06 '09 13:05

Justin Ethier


People also ask

What is the alternative for top in SQL?

There is an alternative to TOP clause, which is to use ROWCOUNT. Use ROWCOUNT with care, as it can lead you into all sorts of problems if it's not turned off.

Can we use top in SQL Server?

Limits the rows returned in a query result set to a specified number of rows or percentage of rows in SQL Server. When you use TOP with the ORDER BY clause, the result set is limited to the first N number of ordered rows. Otherwise, TOP returns the first N number of rows in an undefined order.

What is opposite of top in SQL?

There does not appear to be a keyword opposite of TOP in T-SQL.


1 Answers

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:

  • Oracle: ROW_NUMBER vs ROWNUM
like image 164
Quassnoi Avatar answered Sep 27 '22 19:09

Quassnoi