The OFFSET Keyword

What is it?

OFFSET is most commonly used in conjunction with the LIMIT (or FETCH NEXT) clause for pagination. Pagination is the technique of breaking down a large dataset into smaller, manageable "pages" that can be displayed to users.

LIMIT specifies the maximum number of rows to return.
OFFSET specifies how many rows to skip before the LIMIT is applied.

Example:

SELECT column1, column2
FROM table_name
ORDER BY some_column
LIMIT 10 OFFSET 0; -- Returns first 10 rows

/* Other */
LIMIT 10 OFFSET 10; -- Skips first 10 rows, then returns the next 10

Requires The ORDER BY Keyword for Deterministic Results

If the order is not deterministic, OFFSET will skip an unpredictable set of rows, leading to inconsistent and unreliable results, especially when trying to paginate.

Zero-Based Indexing

An OFFSET of 0 means no rows are skipped, and the result set starts from the very first row.

Case Example:

+-------------+------+
| Column Name | Type |
+-------------+------+
| id | int |
| salary | int |
+-------------+------+

id is the primary key (column with unique values) for this table.
Each row of this table contains information about the salary of an employee.

Write a solution to find the second highest distinct salary from the Employee table. If there is no second highest salary, return null (return None in Pandas).

Solution:

SELECT
    (SELECT DISTINCT salary
     FROM Employee
     ORDER BY salary DESC
     LIMIT 1 OFFSET 1) AS SecondHighestSalary;

Why the SELECT()?

This ensures NULL for "No Results".
The outer SELECT() handles empty subquery results^[1]

When a subquery is placed directly in the SELECT list of an outer query (known as a scalar subquery), if it returns an empty set, the SQL standard dictates that the result of that subquery expression is **automatically converted to `NULL ↩︎