beginner

LIMIT / TOP Clause

By DataPlayArena Technical Review BoardUpdated June 20266 min readRuntime Verified

The LIMIT (or TOP in T-SQL / SQL Server) clause specifies the maximum number of records a SQL query will return. It is essential for optimizing query performance, preventing memory overflow when inspecting large tables, and implementing pagination in web applications.


1. Why Use LIMIT / TOP?

In enterprise data warehouses with billions of rows, running SELECT * FROM sales; without row restrictions can lock database tables, consume gigabytes of network bandwidth, and crash client applications.

Key use cases for limiting rows:

  • Fast Data Sampling: Inspect schema and sample data instantaneously.
  • Top N Ranking: Retrieve top 5 highest earners or top 10 best-selling products (when combined with ORDER BY).
  • Pagination: Load large result sets in pages (e.g., Page 1: rows 1-20, Page 2: rows 21-40).

2. Standard SQL Syntax: LIMIT

In PostgreSQL, MySQL, SQLite, Snowflake, DuckDB, and Spark SQL, the LIMIT clause is placed at the very end of the query.

sql
SELECT employee_id, first_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

Explanation:

  1. ORDER BY salary DESC sorts all employees from highest salary to lowest.
  2. LIMIT 5 restricts the output set to the top 5 records.

3. SQL Server & Sybase Syntax: TOP

Microsoft SQL Server uses the TOP keyword directly after SELECT.

sql
SELECT TOP 5 employee_id, first_name, salary
FROM employees
ORDER BY salary DESC;

You can also specify a percentage of rows in SQL Server:

sql
SELECT TOP 10 PERCENT employee_id, first_name, salary
FROM employees
ORDER BY salary DESC;

4. Pagination with LIMIT & OFFSET

To implement web page navigation (Page 1, Page 2, Page 3), combine LIMIT with OFFSET. OFFSET specifies how many rows to skip before starting to return rows.

sql
-- Page 1: Retrieve rows 1 to 10
SELECT product_id, product_name, price
FROM products
ORDER BY product_id
LIMIT 10 OFFSET 0;

-- Page 2: Skip first 10 rows, retrieve next 10 rows (rows 11-20)
SELECT product_id, product_name, price
FROM products
ORDER BY product_id
LIMIT 10 OFFSET 10;
Important: Always Pair LIMIT with ORDER BY

Without an explicit ORDER BY clause, relational databases return rows in arbitrary physical storage order. Using LIMIT without ORDER BY produces unpredictable, non-deterministic results across query executions.


5. SQL Standard 2008 Syntax: FETCH FIRST

Oracle 12c+, DB2, and modern SQL standards support OFFSET ... FETCH FIRST n ROWS ONLY:

sql
SELECT employee_id, first_name, salary
FROM employees
ORDER BY salary DESC
OFFSET 0 ROWS
FETCH FIRST 5 ROWS ONLY;

6. Summary Comparison Matrix

| Database Engine | Top Rows Syntax | Pagination Syntax | | :--- | :--- | :--- | | PostgreSQL / MySQL / SQLite | LIMIT 10 | LIMIT 10 OFFSET 20 | | Spark SQL / DuckDB | LIMIT 10 | LIMIT 10 OFFSET 20 | | SQL Server (T-SQL) | TOP (10) | OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY | | Oracle (12c+) | FETCH FIRST 10 ROWS ONLY | OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY | | Snowflake | LIMIT 10 | LIMIT 10 OFFSET 20 |

DPA

DataPlayArena Technical Editorial Board

Curated & maintained by enterprise data platform engineers with 10+ years in distributed systems.

Runtime Verification Standards: Every code example, query, and architecture diagram in this tutorial is compiled and verified against real runtimes (Apache Spark 3.5, Python 3.11, PostgreSQL, and DuckDB). If you spot any errata or want to suggest improvements, please submit feedback.