Rajanand Ilangovan
Rajanand's Blog

Follow

Rajanand's Blog

Follow
How to get nth highest salary in SQL Server?

How to get nth highest salary in SQL Server?

SQL interview questions and answers #6

Rajanand Ilangovan's photo
Rajanand Ilangovan
·Mar 9, 2022·

4 min read

You have an employee table with employee details. You need to

  • get 3rd highest salary
  • get the employee details with 3rd highest salary.

Here is the sample data from employees table.

image.png

You can use the below script to create the test data.

USE [TestDB]
GO

--DROP TABLE IF EXISTS dbo.employees;
--GO

CREATE TABLE dbo.employees (
    emp_id INT  PRIMARY KEY,
    emp_name VARCHAR(50) NOT NULL,
    gender CHAR(1) NOT NULL,
    job_title VARCHAR(50) NOT NULL,
    birth_date DATE NOT NULL,
    hire_date DATE NOT NULL,
    salary INT NOT NULL
)
GO

INSERT [dbo].[employees] ([emp_id], [emp_name], [gender], [job_title], [birth_date], [hire_date], [salary]) VALUES (1, N'Sharon Salavaria', N'F', N'Design Engineer', CAST(N'1961-05-02' AS Date), CAST(N'2011-01-18' AS Date), 120000)
INSERT [dbo].[employees] ([emp_id], [emp_name], [gender], [job_title], [birth_date], [hire_date], [salary]) VALUES (2, N'Stephanie Conroy', N'F', N'Network Manager', CAST(N'1984-03-25' AS Date), CAST(N'2009-02-04' AS Date), 40000)
INSERT [dbo].[employees] ([emp_id], [emp_name], [gender], [job_title], [birth_date], [hire_date], [salary]) VALUES (3, N'Christian Kleinerman', N'M', N'Maintenance Supervisor', CAST(N'1976-01-18' AS Date), CAST(N'2008-12-14' AS Date), 110000)
INSERT [dbo].[employees] ([emp_id], [emp_name], [gender], [job_title], [birth_date], [hire_date], [salary]) VALUES (4, N'Annette Hill', N'F', N'Purchasing Assistant', CAST(N'1978-01-29' AS Date), CAST(N'2010-12-06' AS Date), 90000)
INSERT [dbo].[employees] ([emp_id], [emp_name], [gender], [job_title], [birth_date], [hire_date], [salary]) VALUES (5, N'Karen Berg', N'F', N'Application Specialist', CAST(N'1978-05-19' AS Date), CAST(N'2009-02-16' AS Date), 80000)
INSERT [dbo].[employees] ([emp_id], [emp_name], [gender], [job_title], [birth_date], [hire_date], [salary]) VALUES (6, N'Ramesh Meyyappan', N'M', N'Application Specialist', CAST(N'1988-03-13' AS Date), CAST(N'2009-02-03' AS Date), 90000)
INSERT [dbo].[employees] ([emp_id], [emp_name], [gender], [job_title], [birth_date], [hire_date], [salary]) VALUES (7, N'Chris Norred', N'M', N'Control Specialist', CAST(N'1987-05-26' AS Date), CAST(N'2009-03-06' AS Date), 140000)
INSERT [dbo].[employees] ([emp_id], [emp_name], [gender], [job_title], [birth_date], [hire_date], [salary]) VALUES (8, N'Linda Mitchell', N'F', N'Sales Representative', CAST(N'1980-02-27' AS Date), CAST(N'2011-05-31' AS Date), 60000)
INSERT [dbo].[employees] ([emp_id], [emp_name], [gender], [job_title], [birth_date], [hire_date], [salary]) VALUES (9, N'Zainal Arifin', N'M', N'Document Control Manager', CAST(N'1976-01-30' AS Date), CAST(N'2009-01-04' AS Date), 100000)
INSERT [dbo].[employees] ([emp_id], [emp_name], [gender], [job_title], [birth_date], [hire_date], [salary]) VALUES (10, N'Kevin Brown', N'M', N'Marketing Assistant', CAST(N'1987-05-03' AS Date), CAST(N'2007-01-26' AS Date), 90000)
INSERT [dbo].[employees] ([emp_id], [emp_name], [gender], [job_title], [birth_date], [hire_date], [salary]) VALUES (11, N'Sean Chai', N'M', N'Document Control Assistant', CAST(N'1987-03-12' AS Date), CAST(N'2009-01-22' AS Date), 90000)
INSERT [dbo].[employees] ([emp_id], [emp_name], [gender], [job_title], [birth_date], [hire_date], [salary]) VALUES (12, N'Magnus Hedlund', N'M', N'Facilities Administrative Assistant', CAST(N'1971-08-27' AS Date), CAST(N'2009-12-21' AS Date), 130000)
INSERT [dbo].[employees] ([emp_id], [emp_name], [gender], [job_title], [birth_date], [hire_date], [salary]) VALUES (13, N'Terri Duffy', N'F', N'Vice President of Engineering', CAST(N'1971-08-01' AS Date), CAST(N'2008-01-31' AS Date), 180000)
INSERT [dbo].[employees] ([emp_id], [emp_name], [gender], [job_title], [birth_date], [hire_date], [salary]) VALUES (14, N'Peter Connelly', N'M', N'Network Administrator', CAST(N'1980-05-28' AS Date), CAST(N'2009-02-23' AS Date), 70000)
INSERT [dbo].[employees] ([emp_id], [emp_name], [gender], [job_title], [birth_date], [hire_date], [salary]) VALUES (15, N'Amy Alberts', N'F', N'European Sales Manager', CAST(N'1957-09-20' AS Date), CAST(N'2012-04-16' AS Date), 80000)
INSERT [dbo].[employees] ([emp_id], [emp_name], [gender], [job_title], [birth_date], [hire_date], [salary]) VALUES (16, N'A. Scott Wright', N'M', N'Master Scheduler', CAST(N'1968-09-17' AS Date), CAST(N'2008-12-12' AS Date), 10000)
INSERT [dbo].[employees] ([emp_id], [emp_name], [gender], [job_title], [birth_date], [hire_date], [salary]) VALUES (17, N'Hazem Abolrous', N'M', N'Quality Assurance Manager', CAST(N'1977-10-26' AS Date), CAST(N'2009-02-28' AS Date), 120000)
INSERT [dbo].[employees] ([emp_id], [emp_name], [gender], [job_title], [birth_date], [hire_date], [salary]) VALUES (18, N'Mike Seamans', N'M', N'Accountant', CAST(N'1979-07-01' AS Date), CAST(N'2009-03-08' AS Date), 10000)
INSERT [dbo].[employees] ([emp_id], [emp_name], [gender], [job_title], [birth_date], [hire_date], [salary]) VALUES (19, N'Ovidiu Cracium', N'M', N'Senior Tool Designer', CAST(N'1978-01-17' AS Date), CAST(N'2010-12-05' AS Date), 120000)
INSERT [dbo].[employees] ([emp_id], [emp_name], [gender], [job_title], [birth_date], [hire_date], [salary]) VALUES (20, N'Tete Mensa-Annan', N'M', N'Sales Representative', CAST(N'1978-01-05' AS Date), CAST(N'2012-09-30' AS Date), 90000)
GO

SELECT * 
FROM dbo.employees 
ORDER BY salary DESC

The solution is implemented using the below methods

  • RANK function
  • DENSE_RANK function
  • ROW_NUMBER function
  • OFFSET and FETCH option

Get 'N'th highest salary

--- nth highest salary
-- solution #1
;with emp AS (
    SELECT salary,
    RANK() OVER(ORDER BY salary DESC) AS rownum
    FROM dbo.employees 
)
SELECT DISTINCT salary FROM emp WHERE rownum = 3

-- solution #2
;with emp AS (
    SELECT salary,
    DENSE_RANK() OVER(ORDER BY salary DESC) AS rownum
    FROM dbo.employees 
)
SELECT DISTINCT salary FROM emp WHERE rownum = 3

-- solution #3
;with emp AS (
    SELECT salary,
    ROW_NUMBER() OVER(ORDER BY salary DESC) AS rownum
    FROM dbo.employees 
)
SELECT salary FROM emp WHERE rownum = 3

-- solution #4
SELECT salary 
FROM dbo.employees 
ORDER BY salary DESC 
OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY

image.png

Get employee details with 'n' highest salary

--- emp details with nth highest salary
-- solution #1
;with emp AS (
    SELECT *,
    RANK() OVER(ORDER BY salary DESC) AS rownum
    FROM dbo.employees 
)
SELECT emp_id,emp_name,gender,job_title,birth_date,hire_date,salary
FROM emp WHERE rownum = 3

-- solution #2
;with emp AS (
    SELECT *,
    DENSE_RANK() OVER(ORDER BY salary DESC) AS rownum
    FROM dbo.employees 
)
SELECT emp_id,emp_name,gender,job_title,birth_date,hire_date,salary
FROM emp WHERE rownum = 3

-- solution #3
;with emp AS (
    SELECT salary,
    ROW_NUMBER() OVER(ORDER BY salary DESC) AS rownum
    FROM dbo.employees 
)
SELECT emp_id,emp_name,gender,job_title,birth_date,hire_date,salary 
FROM dbo.employees
WHERE salary = (select salary from emp where rownum = 3)

-- solution #4
SELECT emp_id,emp_name,gender,job_title,birth_date,hire_date,salary 
FROM dbo.employees
WHERE salary = (SELECT salary 
                FROM dbo.employees 
                ORDER BY salary DESC  
                OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY)

image.png

Did you find this article valuable?

Support Rajanand Ilangovan by becoming a sponsor. Any amount is appreciated!

See recent sponsors | Learn more about Hashnode Sponsors