Skip to main content

SQL OFFSET Clause for Pagination

· 2 min read
PSVNL SAI KUMAR
PSVNL SAI KUMAR
SDE @ Intralinks

The OFFSET clause in SQL is commonly used to skip a specified number of rows in a query result, often in conjunction with the LIMIT clause to retrieve a specific subset of rows. This is particularly useful for implementing pagination, allowing you to load data in manageable chunks instead of all at once.

Basic Syntax​

Here’s the general syntax of how to use OFFSET with LIMIT in SQL:

SELECT column1, column2, ...
FROM table_name
ORDER BY column_name
LIMIT number_of_rows OFFSET number_of_rows_to_skip;
  • LIMIT specifies the maximum number of rows to retrieve.
  • OFFSET specifies the number of rows to skip before beginning to return rows in the result.

Example: Fetching Rows with OFFSET​

Assume you have an employees table and want to skip the first 10 rows, then retrieve the next 5 rows:

SELECT * 
FROM employees 
ORDER BY employee_id 
LIMIT 5 OFFSET 10;

In this example:

  • The query will start fetching rows from the 11th row based on employee_id ordering.
  • Only 5 rows will be returned.

Pagination with OFFSET and LIMIT​

Pagination can be achieved by specifying different values for OFFSET and LIMIT across multiple pages:

  1. Page 1 (First 10 rows):

    SELECT * FROM employees ORDER BY employee_id LIMIT 10 OFFSET 0;

  2. Page 2 (Next 10 rows):

    SELECT * FROM employees ORDER BY employee_id LIMIT 10 OFFSET 10;

  3. Page 3 (Another 10 rows):

    SELECT * FROM employees ORDER BY employee_id LIMIT 10 OFFSET 20;

This approach enables efficient data retrieval for applications requiring sequential data loading, such as lists or tables that support pagination.

Key Takeaways​

  • OFFSET helps skip over a specified number of rows in the result.
  • Often used with LIMIT for pagination, allowing data to be loaded in manageable portions.
  • Essential for database-driven applications needing paginated displays.

This setup of OFFSET and LIMIT is helpful for web applications where data is displayed in parts, improving both performance and user experience.