sql_top

SQL LIMIT / FETCH / TOP Clause

The SQL Clause for Limiting Result Sets

When querying data, you often need to retrieve only a specific number of rows, or the top N rows based on a certain order. This is crucial for performance on large tables and for features like pagination.

Different database systems use different clauses for this purpose: LIMIT (MySQL, PostgreSQL, SQLite), FETCH FIRST N ROWS ONLY (SQL Standard, Oracle 12c+), and TOP N (SQL Server, MS Access).

Using these clauses can significantly improve query performance by reducing the amount of data transferred and processed, especially on tables with thousands or millions of records.

Syntax Across Different Database Systems

MySQL, PostgreSQL, SQLite: LIMIT Clause

The LIMIT clause restricts the number of rows returned by the query.

SELECT column_name(s)
FROM table_name
WHERE condition
ORDER BY column_name(s)
LIMIT number;

SQL Server / MS Access: TOP Clause

The TOP clause is used to specify the number or percentage of records to return.

SELECT TOP number|PERCENT column_name(s)
FROM table_name
WHERE condition
ORDER BY column_name(s);

Note: ORDER BY is often essential when using TOP, LIMIT, or FETCH to ensure you get a meaningful subset of data (e.g., the top 10 highest scores). Without ORDER BY, the rows returned are not guaranteed to be in any specific order.

Oracle (12c and later) / SQL Standard: FETCH FIRST Clause

The FETCH FIRST N ROWS ONLY clause is the standard SQL way to limit rows. Oracle 12c and later versions support this.

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

Oracle (pre-12c): ROWNUM (Classic Approach)

For older Oracle versions, ROWNUM is commonly used in a subquery, as ROWNUM is applied before ORDER BY if used in the same query level.

SELECT column_name(s)
FROM (
  SELECT column_name(s), ROWNUM as rn
  FROM table_name
  WHERE condition
  ORDER BY column_name(s)
)
WHERE rn <= number;

Demo Table Context

For the examples in this section, imagine a Products table with columns like ProductID, ProductName, SupplierID, CategoryID, UnitPrice, and UnitsInStock.

Examples of Limiting Results

Example: Get the first 5 products (MySQL/PostgreSQL/SQLite)

This query retrieves the first 5 products. The order might be arbitrary if not specified.

SELECT ProductName, UnitPrice
FROM Products
LIMIT 5;

Example: Get the 3 most expensive products (MySQL/PostgreSQL/SQLite)

SELECT ProductName, UnitPrice
FROM Products
ORDER BY UnitPrice DESC
LIMIT 3;

Example: Get the first 5 products (SQL Server)

SELECT TOP 5 ProductName, UnitPrice
FROM Products;

Example: Get the 3 most expensive products (SQL Server)

SELECT TOP 3 ProductName, UnitPrice
FROM Products
ORDER BY UnitPrice DESC;

Example: Get the first 10% of products by price (SQL Server)

SELECT TOP 10 PERCENT ProductName, UnitPrice
FROM Products
ORDER BY UnitPrice DESC;

Example: Get the 3 most expensive products (Oracle 12c+ / SQL Standard)

SELECT ProductName, UnitPrice
FROM Products
ORDER BY UnitPrice DESC
FETCH FIRST 3 ROWS ONLY;

Example: Get the 3 most expensive products (Oracle pre-12c)

SELECT ProductName, UnitPrice
FROM (
  SELECT ProductName, UnitPrice
  FROM Products
  ORDER BY UnitPrice DESC
)
WHERE ROWNUM <= 3;

Practical Application: Pagination

Limiting clauses are fundamental for implementing pagination in applications, where data is displayed in chunks (pages) rather than all at once. For example, to get the second page of 10 products:

MySQL/PostgreSQL Syntax (using OFFSET):

SELECT ProductName, UnitPrice
FROM Products
ORDER BY ProductID
LIMIT 10 OFFSET 10; -- Skip first 10, take next 10

SQL Server Syntax (using OFFSET/FETCH, SQL Server 2012+):

SELECT ProductName, UnitPrice
FROM Products
ORDER BY ProductID
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

Oracle 12c+ Syntax (using OFFSET/FETCH):

SELECT ProductName, UnitPrice
FROM Products
ORDER BY ProductID
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

For more information on pagination strategies in different SQL databases, you might consult their respective documentation or search for ‘SQL pagination techniques’.