Find Nth Highest Value in SQL without Window Functions

-- Find the 3rd highest salary
SELECT salary
FROM employees e1
WHERE 3 = (SELECT COUNT(DISTINCT salary)
           FROM employees e2
           WHERE e2.salary >= e1.salary
          );

-- To find the 3rd lowest, change >= to <= and adjust the number N accordingly.
-- For example, for the 3rd lowest:
-- SELECT salary
-- FROM employees e1
-- WHERE 3 = (SELECT COUNT(DISTINCT salary)
--            FROM employees e2
--            WHERE e2.salary <= e1.salary
--           );

How it works: This SQL snippet demonstrates how to find the Nth highest value (e.g., salary) in a table without relying on window functions. It uses a correlated subquery that counts the number of distinct values greater than or equal to the current value. When this count equals N (e.g., 3 for the 3rd highest), that value is returned. This method is valuable for databases that don't support window functions or when specific performance characteristics are desired.