SQL
Find the Nth Highest Value in a Column
Efficiently retrieve the Nth largest numeric value from a specific column in a SQL table, useful for top N analysis or finding specific ranked items.
-- For MySQL/PostgreSQL (to find the 3rd highest salary)
SELECT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 2; -- OFFSET N-1 for the Nth highest
-- Alternative for SQL Server (using TOP and subquery for Nth highest)
SELECT TOP 1 salary
FROM (
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
) AS sub
ORDER BY salary ASC;
-- For SQL Server Nth highest without DISTINCT, use ROW_NUMBER()
-- SELECT salary FROM (
-- SELECT salary, ROW_NUMBER() OVER (ORDER BY salary DESC) as rn
-- FROM employees
-- ) AS ranked_employees
-- WHERE rn = 3;
How it works: This snippet demonstrates how to find the Nth highest value in a column. For MySQL and PostgreSQL, it uses ORDER BY ... DESC to sort values in descending order and LIMIT 1 OFFSET N-1 to select the desired record (e.g., OFFSET 2 for the 3rd highest). An alternative SQL Server approach uses TOP 1 with a subquery to find the 3rd highest from distinct salaries, or ROW_NUMBER() for a more general solution including duplicates.