Second Highest Salary in SQL

It is one of the most favorite question of the Interviewers. They often asked this type of question like
How to find 2nd/nth Highest salary in SQL
How to find 2nd/nth Highest salary without using TOP keyword
How to find nth Highest salary without using TOP keyword

So here are 7 ways to get 2nd/nth highest salary from a table in SQL. You have several different ways to find the same by using CTE, ROW_NUMBER(), DENSE_RANK(), NTILE(), SIMPLE INNER QUERY etc . Have a look.

Creating an Employee table

CREATE TABLE Employee (name VARCHAR(10), salary INT);

Inserting values in table

INSERT INTO Employee VALUES ('George', 3000);
INSERT INTO Employee VALUES ('John', 4000);
INSERT INTO Employee VALUES ('Bill Gates', 3000);
INSERT INTO Employee VALUES ('Mark', 5000);
INSERT INTO Employee VALUES ('David', 7000);

1. Inner Query with Top Keyword

Simple Query with TOP to fetch Highest Salary form Records

SELECT TOP 1 salary
FROM Employee ORDER BY salary DESC

Simple Query with TOP to fetch nth Salary form Records

SELECT TOP 1 Salary
FROM
 (
   SELECT 
	  DISTINCT TOP 2 Salary
   FROM Employee
      ORDER BY Salary DESC
  ) AS temp
ORDER BY Salary 

TIP –Here 2 is taken as we want 2nd highest salary, change this no. as per your requirement

2. Inner Query without Top Keyword

SELECT 
  DISTINCT SALARY			
FROM Employee Emp1
WHERE (1) = (
			  SELECT COUNT(DISTINCT(Emp2.Salary))
			  FROM Employee Emp2
			  WHERE Emp2.Salary > Emp1.Salary
			)

TIP –
1. In line one DISTINCT is used, in case the inner query return more than one record.
2. The number in braces after WHERE tells the nth number you want to fetch starting from 0. (0 denotes 1st, 1 denotes 2nd ….etc)

3. Using CTE with ROW_NUMBER() function

WITH CTE AS
(
	SELECT 
	  Name, 
	  Salary,
	  ROW_NUMBER() OVER (ORDER BY salary DESC) As sr
	FROM Employee
)
SELECT Name, Salary FROM CTE WHERE sr = 2

TIP :
1. Here 2 represents nth highest salary you want to retrieve.
2. Not ideal when the records are not DISTINCT. USE DENSE_RANK instead

4. Using CTE with DENSE_RANK() function

WITH CTE AS
(
	SELECT 
		Name,
		Salary,
		DENSE_RANK() over(ORDER BY Salary DESC) As sr
	FROM Employee
)
SELECT Name, Salary from CTE where sr = 2

TIP : Here 2 represents nth highest salary you want to retrieve.

5. Using CTE with NTILE() function

WITH Salaries AS
(
	SELECT
		Name, 
		Salary, 
		NTILE(10) OVER(ORDER BY Salary DESC) AS NTile
	FROM
	dbo.Employee
)
SELECT
   Salary FROM  Salaries WHERE  NTile = 2

TIP : Here 2 represents nth highest salary you want to retrieve.

6. Using DENSE_RANK() function

SELECT DISTINCT Emp.Salary
FROM
  (
  	SELECT 
  		Name,
  		Salary,
  		DENSE_RANK() over(ORDER BY Salary desc) AS sr FROM Employee
  ) AS Emp 
	  WHERE sr = 2

TIP : Here 2 represents nth highest salary you want to retrieve.

7. Using ROW_NUMBER() function

SELECT Emp.Salary
FROM
	(
	  SELECT 
		  Name, 
		  Salary,
		  ROW_NUMBER() OVER (ORDER BY Salary DESC) AS sr 
	   FROM Employee
	) AS Emp
      WHERE sr = 2

TIP : Here 2 represents nth highest salary you want to retrieve.