Recently in an interview I was asked to write a query where I had to fetch nth highest salary from a table without using TOP and any sub-query ?
I got totally confused as the only way I knew to implement it uses both TOP and sub-query.
Kindly provide its solution.
Thanks in advance.
Try a CTE - Common Table Expression:
WITH Salaries AS
(
SELECT
SalaryAmount, ROW_NUMBER() OVER(ORDER BY SalaryAmount DESC) AS 'RowNum'
FROM
dbo.SalaryTable
)
SELECT
SalaryAmount
FROM
Salaries
WHERE
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
SalaryAmount
FROM
Salaries
WHERE
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.