PostgreSQL LIMIT

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 CaseQuery
Basic LIMITSELECT * FROM employees LIMIT 5;
Order & LimitSELECT * FROM employees ORDER BY salary DESC LIMIT 5;
Pagination (LIMIT + OFFSET)SELECT * FROM employees LIMIT 10 OFFSET 20;
Top 1 RecordSELECT * FROM employees ORDER BY salary DESC LIMIT 1;
Limit with DISTINCTSELECT DISTINCT department FROM employees LIMIT 3;
Limit with SubquerySELECT * FROM employees WHERE salary = (SELECT MAX(salary) FROM employees);

Would you like a pagination example with a web application? 🚀

Soeng Souy

Soeng Souy

Website that learns and reads, PHP, Framework Laravel, How to and download Admin template sample source code free.

Post a Comment

CAN FEEDBACK
close