SQL OFFSET

What is SQL OFFSET?

Skips a specified number of rows before the query starts returning results.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL OFFSET: PostgreSQL, MySQL 8+, MariaDB, SQLite, SQL Server 2012+, Oracle 12c+, Snowflake, BigQuery, Redshift

SQL OFFSET Full Explanation

OFFSET is an optional clause used with ORDER BY and usually LIMIT or FETCH to implement pagination. After the result set is ordered, the database discards the first N rows defined by OFFSET and returns the remaining rows (or the next M rows when combined with LIMIT/FETCH). OFFSET is evaluated after WHERE, GROUP BY, HAVING, and ORDER BY, so the skipped rows are determined from the final sorted list. Because the database must still compute and sort all preceding rows, large OFFSET values can hurt performance. Use keyset pagination as an alternative when working with very large tables. In SQL Server, Oracle, and ANSI SQL, OFFSET pairs with FETCH NEXT; in PostgreSQL, MySQL, and SQLite it pairs with LIMIT.

SQL OFFSET Syntax

-- PostgreSQL, MySQL, SQLite
SELECT column_list
FROM table_name
ORDER BY sort_expression
LIMIT <row_count> OFFSET <skip_rows>;

-- ANSI SQL / SQL Server / Oracle
SELECT column_list
FROM table_name
ORDER BY sort_expression
OFFSET <skip_rows> ROWS FETCH NEXT <row_count> ROWS ONLY;

SQL OFFSET Parameters

OFFSET INT - Required. Number of rows to skip before returning data.
LIMIT / FETCH INT - Optional but common companion clause that caps how many rows are returned after the offset.

Example Queries Using SQL OFFSET

-- Skip the first 10 orders and return the next 5
SELECT order_id, total
FROM orders
ORDER BY order_date DESC
LIMIT 5 OFFSET 10;

-- SQL Server style: page 3 with 20 rows per page
DECLARE @page INT = 3, @size INT = 20;
SELECT user_id, email
FROM users
ORDER BY created_at
OFFSET (@page-1)*@size ROWS FETCH NEXT @size ROWS ONLY;

Expected Output Using SQL OFFSET

The first query returns 5 rows: orders ranked 11-15 by most-recent order_date
The second query returns rows 41-60 from the users table, based on creation date

Use Cases with SQL OFFSET

Building next/previous page navigation in web apps
Implementing infinite scroll APIs
Sampling segments of a large, ordered dataset for analysis
Skipping header rows in external table queries

Common Mistakes with SQL OFFSET

Omitting ORDER BY, which makes OFFSET nondeterministic
Assuming OFFSET improves performance; it still scans preceding rows
Using large OFFSET values in UI pagination, causing slow queries
Mixing LIMIT-only syntax in SQL Server or FETCH-only syntax in MySQL

First Introduced In

SQL:2008 (OFFSET/FETCH), earlier vendor implementations in PostgreSQL 7.0

Frequently Asked Questions

What is the difference between OFFSET and LIMIT?

LIMIT caps how many rows are returned. OFFSET skips rows before the cap is applied. Together they define a page: OFFSET 20 LIMIT 10 returns rows 21-30.

Can I use OFFSET without ORDER BY?

Technically yes, but the returned rows are unpredictable because relational tables are unordered. Always pair OFFSET with a deterministic ORDER BY.

How can I paginate large tables without slow OFFSETs?

Use keyset (cursor) pagination: filter on the last seen sort key (e.g., WHERE id > last_id) instead of OFFSET. This lets the database leverage indexes.

Does OFFSET start at 0 or 1?

OFFSET counts from zero in all major SQL dialects. OFFSET 0 returns the result set starting from the first row.