How to get only the first rows from query results?
Sometimes you need just to fetch first few rows from the query results, not the whole results set. Different databases offer functions to limit the rows displayed from the query results.
Oracle
SELECT * FROM employees WHERE ROWNUM <= 10
IBM DB2, Oracle (12c), PostgreSQL (v8.3 and later)
SELECT * FROM employees FETCH FIRST 10 ROWS ONLY
Microsoft SQL Server, Sybase (12.5 and later)
SELECT TOP 10 * FROM employees
MySQL, PostgreSQL
SELECT * FROM employees LIMIT 10
How to return top 10 rows in order?
You would need to use the limiting function and sort the results with ORDER BY clause.
IBM DB2, Oracle (12c), PostgreSQL (v8.3 and later)
SELECT * FROM employees ORDER BY salary DESC FETCH FIRST 10 ROWS ONLY
Microsoft SQL Server, Sybase (12.5 and later)
SELECT TOP 10 * FROM employees ORDER BY salary DESC
MySQL, PostgreSQL
SELECT * FROM employees ORDER BY salary DESC LIMIT 10
Oracle
In Oracle database you need to keep in mind that ROWNUM (which is a pseudo-column that enumerates the results set) values are assigned before any sorting or aggregating. That means if we use ORDER BY and WHERE ROWNUM <=10 clauses, Oracle will fetch first 10 rows from the result set and then order those 10 rows by column values. To prevent this, we need to first order the rows in a subquery and then limit the results with WHERE ROWNUM <= 10 clause in the outer query.
SELECT * FROM (SELECT * FROM employees ORDER BY salary DESC) WHERE ROWNUM <= 10