Question: How do you use the limit clause in PostgreSQL to get the top N rows of a query result?

Answer

In SQL, particularly in PostgreSQL, retrieving a subset of rows from a query result is a common requirement. This is especially useful when you need only the top N rows of a result set, based on a specific ordering. The LIMIT clause in PostgreSQL is designed for this purpose.

Using the LIMIT Clause

The LIMIT clause restricts the number of rows returned by a query. Here's the basic syntax:

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC | DESC]
LIMIT N;

• column1, column2, ... are the columns you want to select.
• table_name is the name of the table from which to retrieve data.
• N is the number of rows you want to retrieve.
• ORDER BY specifies how to sort the rows before applying the limit. It's optional but crucial when you want the "top" rows based on a certain order.

Example: Top 5 Employees by Salary

Let's say you have an employees table and you want to select the top 5 employees with the highest salary. Here's how you could do it:

SELECT employee_id, name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

This query will return the 5 employees with the highest salaries, sorted from highest to lowest.

Combining LIMIT with OFFSET for Pagination

Sometimes, in addition to fetching the top N rows, you might want to skip a certain number of rows. This is where the OFFSET clause comes into play, often used for pagination:

SELECT column1, column2, ...
FROM table_name
ORDER BY column1
LIMIT N OFFSET M;

• M is the number of rows to skip before starting to return rows.

For example, to get the second set of 5 employees by salary, you would use:

SELECT employee_id, name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5 OFFSET 5;

This skips the top 5 (highest-paid) employees and returns the next 5.

Important Considerations

1. Performance: Using LIMIT without ORDER BY can be faster since sorting requires extra processing. However, the results might not be meaningful if you're looking for "top" rows based on some criteria.
2. Consistency: If new rows are added between queries that use LIMIT and OFFSET for pagination, the result sets might overlap or miss rows. To avoid this, ensure consistent ordering and consider using conditions that anchor your pagination window.

In summary, the LIMIT clause is a powerful feature in PostgreSQL for controlling the size of the result set. It's most effective when combined with ordering to retrieve the top N rows based on specific criteria.