How to find the third and fifth-highest values in a PostgreSQL table?

To find the third and fifth highest values in a PostgreSQL table, you can use a subquery or window functions.

Let’s consider an example using a table named example with a column value where we want to find these values.

Using a Subquery with DISTINCT and ORDER BY

This method is straightforward and works well for tables with a small number of rows:

SELECT DISTINCT value
FROM example
ORDER BY value DESC
LIMIT 1 OFFSET 2; - For the third highest value

SELECT DISTINCT value
FROM example
ORDER BY value DESC
LIMIT 1 OFFSET 4; - For the fifth highest value

In these queries, unique values from the value column are first selected, then they are sorted in descending order.

The LIMIT 1 OFFSET 2 command returns one value, skipping the two highest values to reach the third.

Similarly, LIMIT 1 OFFSET 4 skips the four highest values to access the fifth.

Using the Window Function ROW_NUMBER()

This method is more flexible and efficient for larger datasets:

WITH RankedValues AS (
 SELECT value, ROW_NUMBER() OVER (ORDER BY value DESC) as rank
 FROM (
 SELECT DISTINCT value
 FROM example
 ) as DistinctValues
)
SELECT value
FROM RankedValues
WHERE rank IN (3, 5);

In this query:

A subquery is first created that selects unique values from value.
The window function ROW_NUMBER() is then applied, which assigns a rank to each value in descending order.

In the outer query from the CTE (WITH query), rows where the rank is 3 or 5 are selected.

Both methods are effective, but using window functions is usually preferred for processing large data volumes due to their high performance and flexibility.

You can choose the method depending on the size of your data and specific query requirements.