Paginate With Offset

Skip earlier rows and return the next slice of a sorted result set.

5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Return the second page of two rows

This example skips the first two sorted rows and returns the next two.

MySQL MariaDB PostgreSQL SQLite

Engine-specific syntax

Source table data

Setup

CREATE TABLE posts (id INT, title VARCHAR(50));

INSERT INTO
  posts (id, title)
VALUES
  (1, 'Alpha'),
  (2, 'Bravo'),
  (3, 'Charlie'),
  (4, 'Delta'),
  (5, 'Echo');

Validated query

SQL

SELECT
  id,
  title
FROM
  posts
ORDER BY
  id
LIMIT
  2
OFFSET
  2;

Expected result

id	title
3	Charlie
4	Delta

SQL Server

Engine-specific syntax

Source table data

Setup

CREATE TABLE posts (id INT, title VARCHAR(50));

INSERT INTO
  posts (id, title)
VALUES
  (1, 'Alpha'),
  (2, 'Bravo'),
  (3, 'Charlie'),
  (4, 'Delta'),
  (5, 'Echo');

Validated query

SQL

SELECT
  id,
  title
FROM
  posts
ORDER BY
  id
OFFSET
  2 ROWS
FETCH NEXT
  2 ROWS ONLY;

Expected result

id	title
3	Charlie
4	Delta

The page contents match, but the pagination syntax differs materially on SQL Server.

Useful when

Where this command helps.

serving page 2 or later in a table UI
walking through large ordered result sets in fixed-size slices

Explanation

What the command is doing.

Offset-based pagination returns one page of rows at a time from an ordered result set. The syntax differs across engines, so this concept is best documented with per-engine SQL while keeping the expected page contents aligned.

Paginate With Offset

Return the second page of two rows

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.