How do you fetch only the first N rows in MySQL, SQL Server and Oracle?
SHORT ANSWER
MySQL and PostgreSQL use LIMIT n, SQL Server uses SELECT TOP n, and the SQL standard (Oracle 12c and later, PostgreSQL, SQL Server) uses FETCH FIRST n ROWS ONLY. Always pair it with ORDER BY.
Explain it simply
Showing the top 5 scorers on a leaderboard needs two things: a sorted list and a cut-off. The cut-off keyword differs by database, but the idea is the same.
Example
Top 5 salaries:
sql
-- MySQL, PostgreSQL, SQLite
SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 5;
-- SQL Server
SELECT TOP 5 name, salary FROM employees ORDER BY salary DESC;
-- SQL standard (Oracle 12c+, PostgreSQL, SQL Server 2012+ with OFFSET)
SELECT name, salary FROM employees
ORDER BY salary DESC
FETCH FIRST 5 ROWS ONLY;Pagination (page 3 with 10 rows per page):
sql
SELECT name FROM employees ORDER BY id LIMIT 10 OFFSET 20;Older Oracle used WHERE ROWNUM <= 5, but ROWNUM is applied before ORDER BY, so you must sort in a subquery first.
What to say in the interview
"LIMIT, TOP and FETCH FIRST all cap the row count. Without ORDER BY the rows returned are arbitrary, so I always sort first. For deep pagination, large OFFSET values get slow because the database still walks the skipped rows."
Common mistakes
- Using LIMIT without ORDER BY and expecting the "first" rows to be stable.