PostgreSQL LIMIT

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 on ORDER 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).
  ✅ Use WHERE conditions instead of large OFFSET 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? 🚀