PostgreSQL – OFFSET Clause
The OFFSET clause is used in combination with the LIMIT clause in a SELECT statement to skip a specified number of rows from the beginning of the result set. It’s often used for implementing pagination or retrieving rows beyond the initial result.
The basic syntax of using the LIMIT and OFFSET clauses is as follows:
SELECT column1, column2, ... FROM table_name WHERE condition ORDER BY some_column LIMIT row_count OFFSET offset_count;Here’s a breakdown of the different parts of the LIMIT and OFFSET clauses:
column1, column2, ...: The columns you want to retrieve data from.table_name: The name of the table from which you want to retrieve data.condition: The optional condition that specifies filtering criteria for the rows you want to retrieve.some_column: The column you want to use for ordering the result set.row_count: The maximum number of rows you want to retrieve in the result set.offset_count: The number of rows to skip before starting to retrieve rows.
For example, let’s say you have a table named orders with columns order_id, order_date, and total_amount. You might write a SELECT statement with LIMIT and OFFSET clauses to retrieve the second page of orders, each page containing 10 orders:
SELECT order_id, order_date, total_amount FROM orders ORDER BY order_date LIMIT 10 OFFSET 10;In this query, we’re retrieving order details for the second page of orders. We skip the first 10 rows and then retrieve the next 10 rows ordered by order_date.
The LIMIT and OFFSET clauses are commonly used for implementing pagination in applications, allowing users to view data in smaller chunks.