How to fetch the nth highest salary from a table without using TOP and sub-query?
Try a CTE - Common Table Expression:
WITH Salaries AS( SELECT SalaryAmount, ROW_NUMBER() OVER(ORDER BY SalaryAmount DESC) AS 'RowNum' FROM dbo.SalaryTable)SELECT SalaryAmountFROM SalariesWHERE RowNum <= 5
This gets the top 5 salaries in descending order - you can play with the
RowNumn value and basically retrieve any slice from the list of salaries.
There are other ranking functions available in SQL Server that can be used, too - e.g. there's
NTILE which will split your results into n groups of equal size (as closely as possible), so you could e.g. create 10 groups like this:
WITH Salaries AS( SELECT SalaryAmount, NTILE(10) OVER(ORDER BY SalaryAmount DESC) AS 'NTile' FROM dbo.SalaryTable)SELECT SalaryAmountFROM SalariesWHERE NTile = 1
This will split your salaries into 10 groups of equal size - and the one with
NTile=1 is the "TOP 10%" group of salaries.