SQL SELECT TOP, LIMIT, FETCH FIRST, ROWNUM – Complete Guide with Examples (2026-27)

✅ What is SQL SELECT TOP / LIMIT?

When a table has thousands or millions of rows, retrieving all of them is slow and often unnecessary. The SELECT TOP / LIMIT / FETCH FIRST / ROWNUM clause solves this by telling the database: "Return only the first N rows, then stop."

✅ Useful for: previewing data, getting top 10 results, building paginated reports, improving query performance on large tables.

✅ Always use with ORDER BY — without ORDER BY, the "first N rows" are unpredictable because rows have no guaranteed order in SQL unless you specify one.

✅ Sample Employees Table (used in all examples):-

✅ SQL Syntax – All Databases

SQL Server SELECT TOP Syntax

SELECT TOP (number) column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column_name;

MySQL PostgreSQL SELECT LIMIT Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column_name
LIMIT number;

Oracle 12c+ FETCH FIRST Syntax

SELECT column1, column2, ...
FROM table_name
ORDER BY column_name
FETCH FIRST number ROWS ONLY;

Oracle (older) ROWNUM Syntax

SELECT column1, column2, ...
FROM table_name
WHERE ROWNUM <= number;

✅ Database Comparison – Which Clause to Use

✅ Example 1 – SELECT TOP (SQL Server)

Return only the first 3 rows from the Employees table:

✅ SELECT Query (SQL Server):-

SELECT TOP (3) *
FROM Employees;

✅ Result Show:-

✅ Example 2 – SELECT LIMIT (MySQL / PostgreSQL)

Return only the first 3 rows using LIMIT:

✅ SELECT Query (MySQL / PostgreSQL):-

SELECT *
FROM Employees
LIMIT 3;

✅ Result Show:-

✅ Example 3 – TOP with ORDER BY (Top 3 Highest Salaries)

Get the 3 highest-paid employees — always combine TOP/LIMIT with ORDER BY for meaningful results:

✅ SELECT Query (SQL Server):-

SELECT TOP (3) *
FROM Employees
ORDER BY Salary DESC;

✅ SELECT Query (MySQL / PostgreSQL):-

SELECT *
FROM Employees
ORDER BY Salary DESC
LIMIT 3;

✅ Result Show:-

✅ Example 4 – SELECT TOP PERCENT (SQL Server Only)

In SQL Server, you can use PERCENT to return a percentage of rows instead of a fixed number:

✅ SELECT Query (SQL Server only):-

-- Return the top 50% of rows (SQL Server only)
SELECT TOP (50) PERCENT *
FROM Employees
ORDER BY Salary DESC;

✅ Result Show (7 rows × 50% = 4 rows returned):-

✅ Example 5 – FETCH FIRST (Oracle 12c+ / DB2)

Oracle 12c and later supports the SQL standard FETCH FIRST syntax:

✅ SELECT Query (Oracle 12c+ / DB2):-

SELECT *
FROM Employees
ORDER BY Salary DESC
FETCH FIRST 3 ROWS ONLY;

✅ Result Show:-

✅ Example 6 – ROWNUM (Older Oracle Versions)

In older Oracle databases (before 12c), use ROWNUM in a WHERE clause:

✅ SELECT Query (Oracle, older versions):-

SELECT *
FROM Employees
WHERE ROWNUM <= 3;

✅ Result Show:-

✅ Example 7 – LIMIT with OFFSET (Pagination)

OFFSET tells SQL how many rows to skip before returning results. This is essential for pagination (showing page 1, page 2, page 3 etc.):

✅ SELECT Query (MySQL / PostgreSQL):-

-- Page 1: First 3 rows (rows 1-3)
SELECT * FROM Employees
ORDER BY ID
LIMIT 3 OFFSET 0;

-- Page 2: Next 3 rows (rows 4-6)
SELECT * FROM Employees
ORDER BY ID
LIMIT 3 OFFSET 3;

-- Page 3: Next 3 rows (rows 7-9)
SELECT * FROM Employees
ORDER BY ID
LIMIT 3 OFFSET 6;

✅ Result Show – Page 2 (LIMIT 3 OFFSET 3):-

✅ OFFSET in SQL Server (FETCH FIRST with OFFSET):-

-- Page 2 in SQL Server / Oracle 12c+
SELECT * FROM Employees
ORDER BY ID
OFFSET 3 ROWS
FETCH NEXT 3 ROWS ONLY;

✅ Key Points to Remember

✅ Always use ORDER BY — without ORDER BY, the rows returned are unpredictable. TOP/LIMIT without ORDER BY does not guarantee any particular rows.

✅ Syntax differs by database — TOP for SQL Server, LIMIT for MySQL/PostgreSQL, FETCH FIRST for Oracle 12c+, ROWNUM for older Oracle.

✅ TOP PERCENT is SQL Server only — MySQL and PostgreSQL do not have a PERCENT option.

✅ ROWNUM in Oracle applies before ORDER BY — always use a subquery when combining ROWNUM with ORDER BY in older Oracle.

✅ Use OFFSET for pagination — LIMIT with OFFSET (or OFFSET … FETCH NEXT in SQL Server) is the standard way to implement paged results.

✅ TOP/LIMIT improves performance — returning fewer rows reduces database memory usage and speeds up query execution on large tables.

✅ Common SQL TOP / LIMIT Mistakes to Avoid