How to Get the Top 10 Values in PostgreSQL

When working with databases, it is common to analyze data by retrieving only the most significant records. PostgreSQL provides several efficient ways to extract the top N values from a dataset. In this blog post, we will explore different techniques for getting the top 10 values in PostgreSQL, with examples to guide you through the process.

1. Using the ORDER BY and LIMIT Clause

The simplest way to retrieve the top 10 values in PostgreSQL is by using the ORDER BY clause along with the LIMIT clause. Here’s a basic example:

SELECT column_name
FROM table_name
ORDER BY column_name DESC
LIMIT 10;

• Explanation:
  • ORDER BY column_name DESC sorts the records in descending order.
  • LIMIT 10 ensures only the first 10 records from the sorted result are retrieved.

Example: Retrieve the top 10 highest salaries from an employees table:

SELECT salary
FROM employees
ORDER BY salary DESC
LIMIT 10;

2. Using the FETCH FIRST Clause

PostgreSQL also supports the SQL standard FETCH FIRST clause, which can be used as an alternative to LIMIT.

SELECT column_name
FROM table_name
ORDER BY column_name DESC
FETCH FIRST 10 ROWS ONLY;

Example: Retrieve the top 10 oldest employees:

SELECT age
FROM employees
ORDER BY age DESC
FETCH FIRST 10 ROWS ONLY;

3. Using Window Functions

If you need to retrieve the top 10 values but keep all the rows in your result set for further processing, window functions are a great option.

SELECT column_name, RANK() OVER (ORDER BY column_name DESC) AS rank
FROM table_name
WHERE rank <= 10;

• Explanation:
  • RANK() OVER (ORDER BY column_name DESC) assigns a rank to each row based on the specified order.
  • You can filter rows using a WHERE clause or a subquery to limit the result to the top 10.

Example: Retrieve the top 10 employees with the highest salaries, including ties:

SELECT employee_id, salary
FROM (
SELECT employee_id, salary, RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees
) ranked
WHERE rank <= 10;

4. Handling Ties with DENSE_RANK

If you want to include ties in the top 10, but you don’t want gaps in the ranking, use DENSE_RANK instead of RANK.

Example: Retrieve the top 10 unique salaries, including all employees with those salaries:

SELECT employee_id, salary
FROM (
SELECT employee_id, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees
) ranked
WHERE rank <= 10;

5. Combining DISTINCT with LIMIT

If you want the top 10 distinct values, you can combine DISTINCT with LIMIT after sorting the data.

Example: Retrieve the top 10 distinct salaries:

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 10;

PostgreSQL provides versatile and efficient methods to retrieve the top N values from a dataset. Whether you need a quick result with LIMIT, a standards-compliant approach with FETCH FIRST, or advanced ranking capabilities with window functions, you can tailor the query to meet your requirements. Try these techniques on your datasets and enjoy seamless data analysis in PostgreSQL!

If you have any questions or need further clarification, feel free to drop a comment below!