SQL LIMIT & OFFSET: Result Paging & Top N Records

When building web applications, blogs, and mobile apps, loading thousands of records on a single page freezes browsers and drains server memory. The LIMIT and OFFSET clauses allow developers to slice query results into manageable pages (e.g., 10 or 25 records per page) and retrieve top-ranking items.

1. Retrieving Top N Records with LIMIT

To retrieve only the top 3 highest-performing students across the entire university:

SELECT student_id, first_name, gpa
FROM students
ORDER BY gpa DESC
LIMIT 3;

2. Implementing Pagination with OFFSET

• LIMIT: Specifies the maximum number of rows to return.

• OFFSET: Specifies the number of rows to skip before beginning to return records.

SELECT column_list
FROM table_name
ORDER BY sort_column
LIMIT page_size OFFSET (page_number - 1) * page_size;

-- Page 1: 10 items per page (rows 1 - 10)
SELECT student_id, first_name, gpa
FROM students
ORDER BY student_id ASC
LIMIT 10 OFFSET 0;

-- Page 2: 10 items per page (rows 11 - 20)
SELECT student_id, first_name, gpa
FROM students
ORDER BY student_id ASC
LIMIT 10 OFFSET 10;

Next, let us learn how to modify existing records safely using the SQL UPDATE Statement!