LIMIT in PostgreSQL

The LIMIT clause in PostgreSQL is used to restrict the number of rows returned by a query.

1. Fetching a Limited Number of Rows

SELECT * FROM employees LIMIT 5;

✅ Returns only the first 5 rows from the employees table.

2. Using LIMIT with ORDER BY

SELECT * FROM employees ORDER BY salary DESC LIMIT 3;

✅ Returns the top 3 highest-paid employees.

3. Using LIMIT with OFFSET

• LIMIT restricts the number of results.
• OFFSET skips a certain number of rows before returning results.

SELECT * FROM employees ORDER BY id ASC LIMIT 5 OFFSET 10;

✅ Skips the first 10 rows and returns the next 5.

4. Getting the Latest Record

SELECT * FROM orders ORDER BY order_date DESC LIMIT 1;

✅ Returns the most recent order.

5. Using LIMIT with a Subquery

SELECT * FROM (SELECT * FROM employees ORDER BY salary DESC LIMIT 10) AS top_employees;

✅ Retrieves the top 10 highest-paid employees using a subquery.

Performance Considerations

• Use LIMIT for pagination in web applications.
• Indexes help improve performance when using ORDER BY with LIMIT.