PostgreSQL LIMIT
Clause
The LIMIT
The clause in PostgreSQL restricts the number of rows a query returns. This is useful for pagination, top N queries, and performance optimization.
1. Basic LIMIT
Syntax
SELECT column1, column2, ...
FROM table_name
LIMIT number_of_rows;
🔹 Restricts the query result to a specific number of rows.
Example: Get the First 5 Employees
SELECT id, name, salary
FROM employees
LIMIT 5;
✅ Returns only the first 5 employees from the table.
2. Using ORDER BY
with LIMIT
Since LIMIT
alone does not guarantee an order, it's often used with ORDER BY
.
Example: Get the 5 Highest-Paid Employees
SELECT id, name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;
✅ Orders employees by salary (highest to lowest) and returns the top 5.
3. Using LIMIT
with OFFSET
(Pagination)
The OFFSET
clause is used to skip a specific number of rows before fetching results.
SELECT column1, column2
FROM table_name
ORDER BY column_name
LIMIT number_of_rows OFFSET number_of_rows_to_skip;
Example: Get Employees 6-10 (Pagination)
SELECT id, name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5 OFFSET 5;
✅ Skips the first 5 rows and retrieves the next 5 rows.
Example: Paginate Employees with Page Number
SELECT id, name, salary
FROM employees
ORDER BY salary DESC
LIMIT 10 OFFSET (5 * (page_number - 1));
✅ Dynamically fetches data for different pages.
- Page 1:
LIMIT 10 OFFSET 0
- Page 2:
LIMIT 10 OFFSET 10
- Page 3:
LIMIT 10 OFFSET 20
4. LIMIT ALL
(Equivalent to No Limit)
SELECT * FROM employees LIMIT ALL;
✅ Returns all rows (same as omitting LIMIT
).
5. Using LIMIT
with Subqueries
Example: Get the Employee with the Highest Salary
SELECT id, name, salary
FROM employees
ORDER BY salary DESC
LIMIT 1;
✅ Fetches only the highest-paid employee.
Alternative: Using Subquery
SELECT * FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);
✅ Returns employees with the highest salary (even if there are ties).
6. Combining LIMIT
with DISTINCT
You can use LIMIT
with DISTINCT
to fetch unique records.
Example: Get the First 3 Unique Department Names
SELECT DISTINCT department
FROM employees
LIMIT 3;
✅ Returns only 3 unique department names.
7. Performance Considerations
Using
LIMIT
without an index onORDER BY
columns can be slow.
✅ Optimize with indexing:CREATE INDEX idx_salary ON employees(salary);
Using a high
OFFSET
value can slow down performance (PostgreSQL still processes all skipped rows).
✅ UseWHERE
conditions instead of largeOFFSET
values when possible.
8. Summary
Use Case | Query |
---|---|
Basic LIMIT | SELECT * FROM employees LIMIT 5; |
Order & Limit | SELECT * FROM employees ORDER BY salary DESC LIMIT 5; |
Pagination (LIMIT + OFFSET ) | SELECT * FROM employees LIMIT 10 OFFSET 20; |
Top 1 Record | SELECT * FROM employees ORDER BY salary DESC LIMIT 1; |
Limit with DISTINCT | SELECT DISTINCT department FROM employees LIMIT 3; |
Limit with Subquery | SELECT * FROM employees WHERE salary = (SELECT MAX(salary) FROM employees); |
Would you like a pagination example with a web application? 🚀