In PostgreSQL, LIMIT and OFFSET are used to control how many records are retrieved from a table and which records are displayed.
These clauses are especially useful when working with tables containing thousands or millions of records.
Real-life example:
Imagine a university database containing 10,000 student records.
The Registrar Office may need to:
PostgreSQL provides LIMIT and OFFSET to perform these tasks.
The LIMIT clause specifies the maximum number of records to retrieve.
Basic syntax:
SELECT column1, column2
FROM table_name
LIMIT number;
Example: Display only the first five student records.
SELECT *
FROM students
LIMIT 5;
This query retrieves a maximum of five records.
Important: Without ORDER BY, PostgreSQL does not guarantee which five records will be returned.
We can combine LIMIT with ORDER BY to retrieve specific records in a defined order.
Example: Display the five youngest students.
SELECT *
FROM students
ORDER BY age ASC
LIMIT 5;
Understand the query:
ORDER BY age ASC sorts students from youngest to oldest.LIMIT 5 retrieves only the first five records.Another example: Display the three students with the highest fees.
SELECT *
FROM students
ORDER BY fee DESC
LIMIT 3;
The OFFSET clause specifies how many records PostgreSQL should skip before returning results.
Basic syntax:
SELECT column1, column2
FROM table_name
OFFSET number;
Example: Skip the first five records.
SELECT *
FROM students
ORDER BY student_id
OFFSET 5;
This query skips the first five records and returns the remaining records.
We can combine LIMIT and OFFSET to retrieve a specific group of records.
Basic syntax:
SELECT column1, column2
FROM table_name
ORDER BY column_name
LIMIT number
OFFSET number;
Example: Display records 6 to 10.
SELECT *
FROM students
ORDER BY student_id
LIMIT 5
OFFSET 5;
Understand the query:
ORDER BY student_id arranges records by student ID.OFFSET 5 skips the first five records.LIMIT 5 retrieves the next five records.Suppose our students table contains the following records:
| Student ID | Student Name |
|---|---|
| 1 | Ali |
| 2 | Ahmad |
| 3 | Usman |
| 4 | Sara |
| 5 | Ayesha |
| 6 | Hassan |
| 7 | Bilal |
| 8 | Zain |
| 9 | Hamza |
| 10 | Fatima |
Let’s understand different queries.
Example 1: First three students
SELECT *
FROM students
ORDER BY student_id
LIMIT 3;
Result: Students 1, 2, and 3.
Example 2: Skip the first three students
SELECT *
FROM students
ORDER BY student_id
OFFSET 3;
Result: Students 4 to 10.
Example 3: Display students 4 to 6
SELECT *
FROM students
ORDER BY student_id
LIMIT 3
OFFSET 3;
Result: Students 4, 5, and 6.
Example 4: Display students 7 to 10
SELECT *
FROM students
ORDER BY student_id
LIMIT 4
OFFSET 6;
Result: Students 7, 8, 9, and 10.
Pagination means dividing a large collection of records into smaller groups called pages.
For example, if a university has 100 student records and we display 10 records per page, we will have 10 pages.
We can calculate the OFFSET using this formula:
\[ \text{OFFSET} = (\text{Page Number} - 1) \times \text{Records Per Page} \]
Suppose we want to display 10 records per page.
| Page Number | LIMIT | OFFSET |
|---|---|---|
| 1 | 10 | 0 |
| 2 | 10 | 10 |
| 3 | 10 | 20 |
| 4 | 10 | 30 |
| 5 | 10 | 40 |
SQL for Page 1:
SELECT *
FROM students
ORDER BY student_id
LIMIT 10 OFFSET 0;
SQL for Page 2:
SELECT *
FROM students
ORDER BY student_id
LIMIT 10 OFFSET 10;
SQL for Page 3:
SELECT *
FROM students
ORDER BY student_id
LIMIT 10 OFFSET 20;
This approach is commonly used in websites and applications to display records page by page.
We can also filter records before applying pagination.
Example: Display the first five Computer Science students.
SELECT *
FROM students
WHERE department = 'Computer Science'
ORDER BY student_id
LIMIT 5;
Example: Display the next five Computer Science students.
SELECT *
FROM students
WHERE department = 'Computer Science'
ORDER BY student_id
LIMIT 5 OFFSET 5;
LIMIT controls the maximum number of returned records.OFFSET skips a specified number of records.ORDER BY should be used to ensure a predictable order.LIMIT and OFFSET do not delete or modify records.Try these queries using the students table.
Example 1: Display the first 10 students.
SELECT *
FROM students
ORDER BY student_id
LIMIT 10;
Example 2: Display the five students with the highest fees.
SELECT *
FROM students
ORDER BY fee DESC, student_id ASC
LIMIT 5;
Example 3: Display students from the second page, assuming 10 records per page.
SELECT *
FROM students
ORDER BY student_id
LIMIT 10 OFFSET 10;
Example 4: Display the first three oldest students.
SELECT *
FROM students
ORDER BY age DESC, student_id ASC
LIMIT 3;
Use the students table created in previous lessons.
Exercise 1: LIMIT
Write queries to:
Exercise 2: OFFSET
Write queries to:
Exercise 3: LIMIT and OFFSET
Write queries to:
Exercise 4: Combine SQL Clauses
Write queries to:
| SQL Clause | Purpose |
|---|---|
LIMIT |
Restricts the number of returned records. |
OFFSET |
Skips a specified number of records. |
ORDER BY |
Sorts the records. |
WHERE |
Filters records. |
Remember:
LIMIT 5 means retrieve a maximum of five records.OFFSET 5 means skip the first five records.LIMIT 5 OFFSET 5 means skip five records and retrieve the next five.Next Lesson: UPDATE Statement in PostgreSQL
Students will learn how to modify existing records in a database table using the UPDATE statement.