filmov
tv
Part 1 How to find nth highest salary in sql
Показать описание
If you are a foodie like me, I am sure you will enjoy the recipes on my friend's YouTube channel. If you find them useful, please subscribe and share to support her. She's really good at what she does.
Link for all dot net and sql server video tutorial playlists
Link for slides, code samples and text version of the video
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.
How to find nth highest salary in SQL Server using a Sub-Query
How to find nth highest salary in SQL Server using a CTE
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
Link for all dot net and sql server video tutorial playlists
Link for slides, code samples and text version of the video
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.
How to find nth highest salary in SQL Server using a Sub-Query
How to find nth highest salary in SQL Server using a CTE
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
0:00:46
How to find your style (Step-by-Step Guide) Part 1
0:57:04
How to Find Jesus in the Old Testament: part 1
0:33:44
How To Find Trout In A River Part 1
0:35:02
Loomis Method, How to Find the Centerline Part 1
0:11:51
Raising Monarchs Part 1 - Finding Eggs (How To Find Monarch Butterfly Eggs)
0:40:15
HOW TO FIND PARTNUMBER IN BOEING AIRCRAFT : PART 1 #boeing
0:32:17
Garden Find 6-Speed V12 BMW E31 850i Revival - Project Marseille: Part 1
0:11:10
We Find Mystery NEST... PART 1 in Backyard by HobbyKidsTV
0:00:42
Let’s Get Cooking Part 1 : Chapati Day
0:00:30
Sing along if you find them attractive 🥵✨ Part. 1
0:09:26
Discovering Homo Naledi: Journey to Find a Human Ancestor, Part 1 | Nat Geo Live
0:37:20
Part 1: Greatest barn find collection known to man | Barn Find Hunter - Ep. 46
0:04:02
Fantastic Beasts Where To Find Them (2016) Suitcase Scene (Part-1) II Best Movie Scene
0:01:02
Part 1 - How to find influencers using Humanz
0:10:14
Find The Alien - Gameplay Walkthrough Part 1 All Levels 1-3 (Android,iOS)
0:37:00
How to find stocks that make big moves Part 1
0:03:31
Part- 2 | Find & Beat🤪 | Funny Video😂
0:41:07
Ferguson TED20 Barn Find, Lets get it going Part 1
0:39:12
How do you find a spiritual father? (Part 1)
0:12:26
How to Find Stocks on Sale: Part 1 | Phil Town
0:00:58
#pov find your matching symbol or else… Part 1
0:16:41
Part 1 - How to Find Coffee Flavor - Le Nez du Cafe Aromas 1-9
0:09:13
Quest To Find A Winner Of Takeshi's Castle | Part 1
0:08:12
Alien Catcher - Gameplay Walkthrough Part 1 Tutorial Find The Alien (iOS,Android)
Комментарии