How to fetch the nth highest salary from a table without using TOP and sub-query

Question

I was recently requested to create a query in an interview to retrieve the nth highest salary from a table without using TOP or any sub-queries. Kindly provide its solution.

Answer 1

Try a CTE - Common Table Expression.

You may fiddle with the RowNumn value to practically extract any slice from the list of salaries. This returns the top 5 salaries in descending order.

There are additional ranking functions in SQL Server that can be utilized, such as NTILE, which will divide your results into n groups as closely as feasible in terms of size.

I hope this helps you.

Answer 2

SELECT DISTINCT salary
FROM employee e1
WHERE n = (
    SELECT COUNT(DISTINCT salary)
    FROM employee e2
    WHERE e2.salary >= e1.salary
);

use self join