PostgreSQL: LIMIT Clause – Control the Number of Results

Welcome to The Coding College, where we make learning PostgreSQL simple and effective! This tutorial will guide you through using the LIMIT clause in PostgreSQL to control the number of rows returned by a query.

What is the LIMIT Clause?

The LIMIT clause is used to constrain the number of rows returned by a query. It’s especially useful when you only need a subset of the data, such as displaying the top results in a report or implementing pagination in web applications.

Syntax

SELECT column1, column2, ...
FROM table_name
[WHERE condition]
LIMIT row_count;

• row_count: The maximum number of rows to return.

Example: Sample Table

Let’s work with a table named employees:

Using the LIMIT Clause

Example 1: Fetch the Top 3 Employees

SELECT * FROM employees
LIMIT 3;

Result:

Combining LIMIT with ORDER BY

The LIMIT clause is often used alongside ORDER BY to return specific subsets of sorted data, such as the highest salaries or youngest employees.

Example 2: Top 2 Employees by Salary

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

Result:

Pagination with LIMIT and OFFSET

Use LIMIT with OFFSET to retrieve data in chunks. This is particularly useful for paginating results in web applications.

Example 3: Pagination Example (Rows 3 to 4)

SELECT *
FROM employees
ORDER BY id ASC
LIMIT 2 OFFSET 2;

Explanation:

• LIMIT 2: Fetch 2 rows.
• OFFSET 2: Skip the first 2 rows.

Result:

Dynamic Querying

The LIMIT value can also be parameterized in applications to create flexible queries.

Example 4: Fetch Top N Employees by Age

SELECT name, age
FROM employees
ORDER BY age ASC
LIMIT 3;

Result:

Real-World Applications of LIMIT

1. Dashboard Reports: Display the top-performing employees or departments.
2. Pagination: Load data incrementally for improved user experience.
3. Testing Queries: Limit data during development to quickly check query outputs.

Learn More at The Coding College

Explore more PostgreSQL tutorials and advanced features at The Coding College. Our content focuses on user benefits while aligning with Google’s E-E-A-T guidelines for accuracy and expertise.

Conclusion

The LIMIT clause in PostgreSQL is a powerful tool for controlling query results. By combining it with other SQL features, you can create efficient and user-friendly applications.

Stay tuned to The Coding College for more SQL and programming tutorials!