Comprehensive Guide to PostgreSQL LIMIT Clause

PostgreSQL LIMIT Clause: Restrict Query Results

The PostgreSQL LIMIT clause is used to control the number of rows returned by a query. This is particularly useful when working with large datasets or implementing pagination in applications. The LIMIT clause is often paired with the OFFSET clause to skip a certain number of rows before fetching the desired rows.

Syntax

SELECT column1, column2, ...
FROM table_name
LIMIT number_of_rows OFFSET number_to_skip;

Parameters:

• LIMIT: Specifies the maximum number of rows to return.
• OFFSET: Specifies the number of rows to skip before starting to return rows (optional).

Examples:

1. Fetch a Fixed Number of Rows

Retrieve the first 5 rows from a table.

SQL Query:

Code:

SELECT * 
FROM employees
LIMIT 5;

Code Explanation:

• SELECT *: Selects all columns.
• LIMIT 5: Restricts the output to the first 5 rows.

2. Implement Pagination Using LIMIT and OFFSET

Fetch rows 6 to 10 (useful for pagination).

SQL Query:

Code:

SELECT * 
FROM employees
LIMIT 5 OFFSET 5;

Code Explanation:

• LIMIT 5: Retrieves a maximum of 5 rows.
• OFFSET 5: Skips the first 5 rows before starting to fetch data.

3. Use with ORDER BY

When combined with ORDER BY, LIMIT ensures consistent and meaningful results.

SQL Query:

Code:

SELECT * 
FROM employees
ORDER BY salary DESC
LIMIT 3;

Code Explanation:

• ORDER BY salary DESC: Sorts employees by salary in descending order.
• LIMIT 3: Returns the top 3 highest-paid employees.

4. Fetch the Highest N Values

Retrieve the top 3 employees with the highest salaries.

SQL Query:

Code:

SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

Result:

Name	Salary
Alice	120,000
Bob	110,000
Charlie	100,000

Advanced Use Cases

1. Subqueries with LIMIT

Extract a specific range of data using subqueries.

SQL Query:

Code:

SELECT *
FROM (SELECT * FROM employees ORDER BY join_date ASC LIMIT 10) subquery
ORDER BY name;

Explanation:

• The inner query fetches the first 10 employees by joining date.
• The outer query sorts these employees alphabetically by name.

2. LIMIT with Aggregate Functions

Find the top 2 departments with the highest average salaries.

SQL Query:

Code:

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC
LIMIT 2;

Explanation:

• GROUP BY department: Groups data by department.
• AVG(salary): Calculates average salary for each department.
• LIMIT 2: Retrieves the top 2 departments with the highest averages.

Performance Tips

1. Indexes: Ensure proper indexing of the columns used in ORDER BY for faster query execution.

2. OFFSET: Avoid high values of OFFSET in large datasets as it can lead to slower performance. Use key-based pagination as an alternative.

3. Testing: Always test queries on sample datasets to validate performance and correctness.

Common Use Cases

• Pagination: Displaying a limited number of rows per page in web applications.
• Top N Records: Fetching the highest or lowest N rows based on a specific column.
• Sampling: Retrieving a small, random subset of data for analysis or debugging.

All PostgreSQL Questions, Answers, and Code Snippets Collection.