PostgreSQL LIMIT / TOP

The LIMIT clause in PostgreSQL is used to restrict the number of rows returned by a query. Unlike some other databases that use TOP, PostgreSQL uses LIMIT. It is useful when you want to fetch only a subset of rows, such as the first few results or for pagination.

1. Basic Syntax

SELECT column1, column2
            FROM table_name
            LIMIT number_of_rows;

- number_of_rows specifies the maximum number of rows to return.

2. Example: LIMIT

-- Get the first 5 employees
            SELECT name, department, salary
            FROM employees
            LIMIT 5;

3. Using LIMIT with OFFSET

The OFFSET clause is used to skip a number of rows before returning results. This is commonly used for pagination.

-- Get 5 employees, skipping the first 10
            SELECT name, department, salary
            FROM employees
            ORDER BY salary DESC
            LIMIT 5 OFFSET 10;

4. LIMIT with ORDER BY

Combining LIMIT with ORDER BY allows you to retrieve the top or bottom rows based on a specific column.

-- Get top 3 highest-paid employees
            SELECT name, salary
            FROM employees
            ORDER BY salary DESC
            LIMIT 3;

            -- Get 3 lowest-paid employees
            SELECT name, salary
            FROM employees
            ORDER BY salary ASC
            LIMIT 3;

5. Using LIMIT with Subqueries

LIMIT can also be used inside subqueries to retrieve a subset of data.

-- Get departments with top 2 highest salaries
            SELECT department_id, name, salary
            FROM employees e
            WHERE e.id IN (
                SELECT id
                FROM employees
                WHERE department_id = e.department_id
                ORDER BY salary DESC
                LIMIT 2
            );

6. Pagination Example

You can use LIMIT and OFFSET to implement pagination in your applications.

-- Page 1 (rows 1-10)
            SELECT * FROM employees
            ORDER BY id
            LIMIT 10 OFFSET 0;

            -- Page 2 (rows 11-20)
            SELECT * FROM employees
            ORDER BY id
            LIMIT 10 OFFSET 10;

            -- Page 3 (rows 21-30)
            SELECT * FROM employees
            ORDER BY id
            LIMIT 10 OFFSET 20;

7. Best Practices

• Always use ORDER BY with LIMIT to ensure consistent results.
• Use OFFSET for pagination but be aware that large offsets may slow down queries.
• For large datasets, consider using keyset pagination for better performance.
• Do not rely on LIMIT without ORDER BY, as row order is not guaranteed.

Conclusion

The LIMIT clause in PostgreSQL is an essential tool to control the number of rows returned. It is widely used for fetching top results, implementing pagination, or testing queries on a subset of data. In the next tutorial, we will explore Subqueries, which allow embedding one query within another for complex data retrieval.