SQL - Top Clause

SQL - Limiting Result Sets

When working with large tables, you often don’t need to retrieve every single row. Limiting the number of rows returned by a query is essential for performance, pagination, and top-N analysis (like finding the ‘Top 10 products’). While different database systems have historically used different syntax, the SQL standard now provides a unified approach.

The SQL Standard: FETCH FIRST Clause

The FETCH FIRST N ROWS ONLY clause, introduced in SQL:2008, is the standard, portable way to limit query results. It’s typically used with ORDER BY to ensure the results are meaningful and deterministic. Many modern databases like PostgreSQL, Oracle, and DB2 support this syntax.

Syntax

SELECT column_list
FROM table_name
ORDER BY sort_expression
OFFSET M ROWS
FETCH FIRST N ROWS ONLY;

• ORDER BY: Crucial for defining which rows are the ‘first’. Without it, the returned rows are arbitrary.
• OFFSET M ROWS: Optional. Skips the first M rows before starting to fetch.
• FETCH FIRST N ROWS ONLY: Specifies the maximum number of rows to return.

Example: Finding the 5 Most Expensive Products

Using a products table:

SELECT name, price
FROM products
ORDER BY price DESC
FETCH FIRST 5 ROWS ONLY;

Vendor-Specific Syntax: LIMIT and TOP

While the standard is preferred for new, portable code, you will frequently encounter vendor-specific syntax in existing projects and different database systems.

MySQL & PostgreSQL: LIMIT

These popular databases use the LIMIT clause, which is very concise.

SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 5;

SQL Server: TOP

SQL Server uses the TOP(N) clause within the SELECT statement. Note that modern SQL Server also supports the standard OFFSET/FETCH syntax, which is often preferred for pagination.

SELECT TOP(5) name, price
FROM products
ORDER BY price DESC;

Implementing Pagination

Pagination—displaying results in pages (e.g., 10 items per page)—is a primary use case for limiting results. This is achieved by combining a limit with an offset.

Example: Get Page 3 of Products (10 per page)

To get the third page, we need to skip the first two pages (2 * 10 = 20 rows) and then fetch the next 10.

Standard SQL / PostgreSQL / SQL Server (Modern):

SELECT name, price
FROM products
ORDER BY name
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;

MySQL:

SELECT name, price
FROM products
ORDER BY name
LIMIT 10 OFFSET 20;

Fetching Rows with Ties

What if you ask for the top 5 most expensive products, but the 5th and 6th products have the exact same price? By default, the database will arbitrarily pick one to be the 5th. The WITH TIES option solves this by including all rows that have the same value in the ORDER BY columns as the last row.

Example

If you want the top 5 products by price, but also want to include any other products that share the same price as the 5th one, you would use:

-- Standard SQL / SQL Server
SELECT name, price
FROM products
ORDER BY price DESC
FETCH FIRST 5 ROWS WITH TIES;

This query might return 6 or 7 rows if multiple products are tied for the 5th highest price.

Performance and Best Practices

• Always use ORDER BY: Limiting results without a deterministic order is generally a bug, as you might get different rows each time you run the query.
• Index for Performance: For ORDER BY ... LIMIT queries to be fast, you must have an index on the column(s) you are ordering by. Without an index, the database may have to sort the entire table before it can pick the top rows, which is very slow on large tables.
• Keyset Pagination: For very large datasets, OFFSET-based pagination can become slow on deep pages (e.g., page 50,000) because the database still has to scan and count all the rows to be skipped. A more advanced technique called ‘keyset pagination’ or ‘seek method’ can be used for better performance, using a WHERE clause on the ordered column (e.g., WHERE created_at > [last_timestamp_from_previous_page]).
• Limiting DELETE or UPDATE: While some databases allow limiting DELETE or UPDATE statements (e.g., DELETE ... LIMIT 100), this can be risky. A safer pattern is to SELECT the primary keys of the rows you want to affect into a temporary list or table, and then use that list in the WHERE clause of your DELETE/UPDATE statement (e.g., DELETE FROM table WHERE id IN (subquery)).