Suggested Video Tutorials:
SQL Server Tutorial
ASP.NET Tutorial
WCF Tutorial
This is a very common SQL Server Interview Question. There are several ways of finding the nth highest salary.
By the end of this video, we will be able to answer all the following questions as well.
1. How to find nth highest salary in SQL Server using a Sub-Query
2. How to find nth highest salary in SQL Server using a CTE
3. How to find the 2nd, 3rd or 15th highest salary
Let's use the following Employees table for this demo.
Use the following script to create Employees table
To find the highest salary it is straight forward. We can simply use the Max() function as shown below.
To find nth highest salary using CTE
To find 2nd highest salary we can use any of the above queries. Simple replace N with 2.
Similarly, to find 3rd highest salary, simple replace N with 3.
Please Note: On many of the websites, you may have seen that, the following query can be used to get the nth highest salary. The below query will only work if there are no duplicates.
SQL Server Tutorial
ASP.NET Tutorial
WCF Tutorial
This is a very common SQL Server Interview Question. There are several ways of finding the nth highest salary.
By the end of this video, we will be able to answer all the following questions as well.
1. How to find nth highest salary in SQL Server using a Sub-Query
2. How to find nth highest salary in SQL Server using a CTE
3. How to find the 2nd, 3rd or 15th highest salary
Let's use the following Employees table for this demo.
Use the following script to create Employees table
Create table Employees
(
ID int primary
key identity,
FirstName nvarchar(50),
LastName nvarchar(50),
Gender nvarchar(50),
Salary int
)
GO
Insert into Employees values ('Ben', 'Hoskins', 'Male', 70000)
Insert into Employees values ('Mark', 'Hastings', 'Male', 60000)
Insert into Employees values ('Steve', 'Pound', 'Male', 45000)
Insert into Employees values ('Ben', 'Hoskins', 'Male', 70000)
Insert into Employees values ('Philip', 'Hastings', 'Male', 45000)
Insert into Employees values ('Mary', 'Lambeth', 'Female', 30000)
Insert into Employees values ('Valarie', 'Vikings', 'Female', 35000)
Insert into Employees values ('John', 'Stanmore', 'Male', 80000)
GO
To find the highest salary it is straight forward. We can simply use the Max() function as shown below.
Select Max(Salary) from Employees
To get the second highest salary use a sub query along with Max() function as shown below.
Select Max(Salary) from Employees where Salary < (Select Max(Salary) from Employees)
To find nth highest salary using Sub-Query
SELECT TOP 1 SALARY
FROM (
SELECT DISTINCT TOP N SALARY
FROM
EMPLOYEES
ORDER BY SALARY DESC
) RESULT
ORDER BY SALARY
To find nth highest salary using CTE
WITH RESULT AS
(
SELECT
SALARY,
DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DENSERANK
FROM
EMPLOYEES
)
SELECT TOP 1 SALARY
FROM RESULT
WHERE DENSERANK = N
To find 2nd highest salary we can use any of the above queries. Simple replace N with 2.
Similarly, to find 3rd highest salary, simple replace N with 3.
Please Note: On many of the websites, you may have seen that, the following query can be used to get the nth highest salary. The below query will only work if there are no duplicates.
WITH RESULT AS
(
SELECT
SALARY,
ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS ROWNUMBER
FROM
EMPLOYEES
)
SELECT SALARY
FROM RESULT
WHERE ROWNUMBER = 3
Can anyone help...
ReplyDeleteBelow is my problem statement
I am a developer and using SQL Server as a backend. I recently installed Windows 8.1 OS from Windows 7.
I was able to successfully installed the Visual Studio 2013. But when I am trying to install SQL server 2014 its giving me an error stating "This SQL Server Setup media is not supported on a X86 system . To continue , run the SQL Server Setup media that matches your system ".
And to a Surprise I am having X64 processor laptop..
Please help me to install SQL server...
You may need to install 32 bit version of sql server. Looks like you are trying to install sql server 64 bit on a 32 bit machine.
DeleteI got d reason .. mine system was 32 bit...
ReplyDeleteDownload sql server 2014 64 bit and try
ReplyDeletecan anybody share sql server 2012 download link...
ReplyDeleteI wish you could type last 5 words of your query in google. :P
Deletein the last query -> ROW_NUMBER()
ReplyDeletecan't we use DISTINCT keyword to avoid duplicate results ???
U can use...I
Deleteused distinct
Could you please tell me where to use DISTINCT keyword?
DeletePls Explain difference b/w implicit transaction & explicit transaction ?
ReplyDeleteHello Sir,
ReplyDeleteWill you please explain query for "To find nth highest salary using Sub-Query"
CREATE PROCEDURE spNthHighestSalary
ReplyDelete@N int
AS
BEGIN
SELECT TOP 1 Salary AS NthHighSalary FROM
(
SELECT DISTINCT TOP (@N) Salary
FROM tblEmployees
ORDER BY Salary DESC
)
Result
ORDER BY Salary
END
select min(Salary) as [N th Salary] from
ReplyDelete(select distinct top (@position) Salary from Employees order by Salary desc)
Employees;
can I use like this??
correlated query also we can use to find N th highest value
ReplyDeleteWhat if i am required to find out the record of the Employee having the highest Salary?
ReplyDelete-- Another option...
DeleteWITH Result (
[ID]
, [FirstName]
, [LastName]
, [Gender]
, Salary
, DenseRank
)
AS (
SELECT [ID]
, [FirstName]
, [LastName]
, [Gender]
, Salary
, DENSE_RANK() OVER (
ORDER BY Salary DESC
) AS DenseRank
FROM [Inventory].[dbo].Employees
)
SELECT TOP 1 * --Salary, [ID], [FirstName], [LastName], [Gender]
FROM Result
WHERE DenseRank = N; -- Replace N with a number.
select * from(
ReplyDeleteselect Distinct salary,ROW_NUMBER() over(order by salary desc) RowId from Employees)result where RowId=3
instead of row_number use dense_rank function.
ReplyDelete