# nth highest salary without using TOP and sub-query from the following table

Discussion in 'ALL SQL SERVER QUESTIONS' started by recherche, Apr 27, 2012.

1. ### rechercheNew Member

Hi SQL Server Experts,

I want to get the nth highest salary without using TOP and sub-query from the following table.

USE [master];
GO
IF EXISTS(SELECT NAME FROM SYS.DATABASES WHERE NAME = 'empdb')
DROP DATABASE empdb;
GO
CREATE DATABASE empdb;
GO
USE [empdb];
GO
IF EXISTS(SELECT NAME FROM SYS.TABLES WHERE NAME = 'Employee')
DROP TABLE dbo.Employee;
GO
CREATE TABLE dbo.Employee
(ID int, Name NVARCHAR(50), Salary Money);
GO
INSERT INTO dbo.Employee
VALUES
(1, 'A', 10000),
(2, 'B', 8000),
(3, 'C', 8000),
(4, 'D', 6000),
(5, 'E', 6000),
(6, 'F', 6000),
(7, 'G', 5000), --4th Highest Salary
(8, 'H', 5000),
(9, 'I', 5000),
(10, 'J', 5000),
(11, 'K', 4000),
(12, 'L', 4000),
(13, 'M', 3000),
(14, 'N', 3000),
(15, 'O', 1000);
GO

--Nth Highest Salary
-- N = 4
SELECT TOP 1 Salary FROM
(
SELECT DISTINCT TOP 4 Salary FROM Employee
ORDER BY Salary DESC
)
A ORDER BY Salary

The answer I can think of for now is:

WITH Salaries AS
(
SELECT
Salary, NTILE(10) OVER(ORDER BY Salary DESC) AS 'NTile'
FROM
Employee
)
SELECT
Salary
FROM
Salaries
WHERE
NTile = 5

Any optimal answer?

Thanks
2. ### FrankKalisModerator

Welcome to the forum!
Is this a real-world problem or some sort of homework asignment?
3. ### rechercheNew Member

Excuse me, school teacher. The question is for SQL Server experts.
4. ### Luis MartinModerator

Frank is the second member with more answers. School teacher?, if so I'm a student.
5. ### rechercheNew Member

Thought this forum was about SQL Server Q&A. Don't know why the replies are so "pedagogical". Anywise, I am just a learner who wanted to hear it straight from the horse's mouth, I mean the SQL Server experts!
6. ### rechercheNew Member

I guess the answer is
;WITH CTE AS
(
SELECT Salary, rnk=DENSE_RANK() OVER (ORDER BY Salary DESC) FROM Employee
)
SELECT DISTINCT Salary FROM CTE WHERE rnk=5

Is there any other optimal answer?
7. ### FrankKalisModerator

It's amazing what you get when you do a little bit of investigation on your own, isn't it...?
Anyway, to avoid the DISTINCT in your solution, I would probably change this to a TOP 1, since you know that all the values in the bucket have to be identical anyway, so any value in the result set is just as good as any other value and TOP 1 just grabs one of them, while DISTINCT does imply a sort and therefore might be more costly without any obvious benefit.
8. ### rechercheNew Member

Thanks for the input .