How do you fetch the second highest value in a table using a subquery?

One common approach is to use a subquery that selects the maximum value less than the overall maximum:

SELECT MAX(col) AS second_highest
FROM my_table
WHERE col < (SELECT MAX(col) FROM my_table);

Alternatively, you can use window functions for clarity and flexibility:

SELECT col
FROM (
  SELECT col, ROW_NUMBER() OVER (ORDER BY col DESC) AS rn
  FROM my_table
) t
WHERE rn = 2;

Both queries run in O(n) time and O(1) additional space (ignoring the temporary row number column). The window‑function version is often preferred for readability and when you need more than just the second highest value.