SQL
Finding the Nth Highest Value in SQL Without Window Functions
Discover how to retrieve the Nth highest or lowest value from a SQL column using subqueries and `COUNT` without relying on window functions or `OFFSET`.
-- Create a sample table
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
salary DECIMAL(10, 2)
);
-- Insert sample data
INSERT INTO employees (name, salary) VALUES
('Alice', 60000.00),
('Bob', 75000.00),
('Charlie', 60000.00),
('David', 90000.00),
('Eve', 75000.00),
('Frank', 100000.00);
-- Find the 1st highest salary (highest)
SELECT DISTINCT salary
FROM employees e1
WHERE 1 = (SELECT COUNT(DISTINCT salary) FROM employees e2 WHERE e2.salary >= e1.salary);
-- Find the 2nd highest salary
SELECT DISTINCT salary
FROM employees e1
WHERE 2 = (SELECT COUNT(DISTINCT salary) FROM employees e2 WHERE e2.salary >= e1.salary);
-- Find the 3rd highest salary
SELECT DISTINCT salary
FROM employees e1
WHERE 3 = (SELECT COUNT(DISTINCT salary) FROM employees e2 WHERE e2.salary >= e1.salary);
-- To find the Nth lowest salary, reverse the comparison in the subquery
-- Find the 2nd lowest salary
SELECT DISTINCT salary
FROM employees e1
WHERE 2 = (SELECT COUNT(DISTINCT salary) FROM employees e2 WHERE e2.salary <= e1.salary);
How it works: This snippet provides a method to find the Nth highest (or lowest) distinct value in a column without using advanced window functions or `LIMIT`/`OFFSET` (which are often restricted or can behave differently for non-distinct values). The approach uses a correlated subquery: for each distinct salary, it counts how many distinct salaries are greater than or equal to it. If this count equals N, then that salary is the Nth highest. The logic can be easily adapted for the Nth lowest by reversing the comparison operator (`<=`).