SQL
Retrieve the Nth Highest Value Using a Correlated Subquery
Learn to find the Nth highest value in a dataset, such as the 3rd highest salary, using a SQL correlated subquery without relying on window functions or pagination clauses.
SELECT
salary
FROM
employees e1
WHERE
3 = (SELECT COUNT(DISTINCT salary) FROM employees e2 WHERE e2.salary >= e1.salary)
LIMIT 1;
How it works: This SQL query finds the 3rd highest unique salary from the `employees` table using a correlated subquery. The subquery counts how many distinct salaries are greater than or equal to the current employee's salary (`e1.salary`). By setting this count equal to '3', we identify the salary for which there are exactly three unique salaries greater than or equal to it, thus the 3rd highest. The `LIMIT 1` is added to ensure only one row is returned, useful if multiple employees share the Nth highest salary.