SELECT 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?

9 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.