sql-top-limit-or-rownum-clause

SQL - Limiting Result Sets

When querying large tables, you often need to fetch only a subset of rows, such as the top N records or a specific ‘page’ of results. The SQL standard provides a way to do this, but many popular databases implemented their own, non-standard clauses first.

Standard SQL: FETCH FIRST Clause

The ANSI SQL standard (since SQL:2008) provides the FETCH FIRST N ROWS ONLY clause. It is used in combination with ORDER BY to ensure you get a predictable, stable subset of results.

Syntax

SELECT column_name(s)
FROM table_name
WHERE [condition]
ORDER BY column_name
FETCH FIRST number ROWS ONLY;

This syntax is supported by modern versions of PostgreSQL, Oracle, and DB2.

Common Vendor-Specific Clauses

While the standard exists, you will frequently encounter vendor-specific syntax in existing codebases.

MySQL / PostgreSQL: LIMIT and OFFSET

MySQL and PostgreSQL use the LIMIT clause. It’s often paired with OFFSET for pagination.

-- Fetch the first N rows
SELECT * FROM table_name ORDER BY column DESC LIMIT 10;

-- Fetch 10 rows, starting from the 21st row (for page 3 if page size is 10)
SELECT * FROM table_name ORDER BY column DESC LIMIT 10 OFFSET 20;

SQL Server: TOP

SQL Server uses the TOP clause. To achieve pagination, you must use the more verbose OFFSET ... FETCH syntax in modern versions.

-- Fetch the top N rows
SELECT TOP 10 * FROM table_name ORDER BY column DESC;

-- Pagination in modern SQL Server
SELECT * FROM table_name ORDER BY column DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

Oracle (Legacy): ROWNUM

Older Oracle versions used a pseudo-column called ROWNUM. This syntax is notoriously tricky and has been superseded by the standard FETCH FIRST clause.

-- Legacy Oracle syntax to fetch the top N rows
SELECT * FROM (
    SELECT * FROM table_name ORDER BY column DESC
) WHERE ROWNUM <= 10;

Example: Getting the Top 3 Highest Paid Customers

Let’s use our Customers table. We want to find the top 3 customers with the highest salaries.

+----+----------+-----+-------------+----------+
| ID | Name     | Age | City        | Salary   |
+----+----------+-----+-------------+----------+
|  1 | Ramesh   |  32 | Ahmedabad   |  2000.00 |
|  2 | Khilan   |  25 | Delhi       |  1500.00 |
|  3 | Kaushik  |  23 | Kota        |  2000.00 |
|  4 | Chaitali |  25 | Mumbai      |  6500.00 |
|  5 | Hardik   |  27 | Bhopal      |  8500.00 |
|  6 | Komal    |  22 | Hyderabad   |  4500.00 |
|  7 | Muffy    |  24 | Indore      | 10000.00 |
+----+----------+-----+-------------+----------+

The key is to always use ORDER BY to define what ‘top’ means.

MySQL / PostgreSQL Example

SELECT ID, Name, Salary
FROM Customers
ORDER BY Salary DESC
LIMIT 3;

SQL Server Example

SELECT TOP 3 ID, Name, Salary
FROM Customers
ORDER BY Salary DESC;

Standard SQL / Modern Oracle Example

SELECT ID, Name, Salary
FROM Customers
ORDER BY Salary DESC
FETCH FIRST 3 ROWS ONLY;

All three queries would produce the same correct result:

+----+--------+----------+
| ID | Name   | Salary   |
+----+--------+----------+
|  7 | Muffy  | 10000.00 |
|  5 | Hardik |  8500.00 |
|  4 | Chaitali |  6500.00 |
+----+--------+----------+