LIMIT and OFFSET

LIMIT

Limits how many rows are returned. Example: LIMIT 5 returns only 5 rows.

OFFSET

Skips a number of rows before returning results. Example: OFFSET 3 skips the first 3 rows.

LIMIT and OFFSET syntax

SELECT * FROM table_name ORDER BY column_name LIMIT 5 OFFSET 10;

LIMIT example

SELECT full_name, gpa FROM Students_Spring2024 ORDER BY gpa ASC LIMIT 3; Returns the 3 lowest GPAs.

Why ORDER BY matters

Use ORDER BY before LIMIT/OFFSET for consistent pages. Without sorting, row order may not be reliable.

First page example

Page size 8: ORDER BY student_id LIMIT 8 OFFSET 0;

Second page example

Page size 8: ORDER BY student_id LIMIT 8 OFFSET 8;

Third page example

Page size 4: ORDER BY major LIMIT 4 OFFSET 8; Skips pages 1 and 2, then returns page 3.

Pagination formula

OFFSET = (page number − 1) × page size. Example: page 3 with 4 rows per page = OFFSET 8.

LIMIT vs. OFFSET

LIMIT controls how many rows you get; OFFSET controls how many rows you skip first.