Problem: How to find second highest salary from Employee table.

Concept: We need to find out the second highest salary from employee table. Below are showing two ways to achieve this.

Method 1: Using SUB Query

In this we have two query parent and child query. Using child query we are going to find maximum  salary. In parent query we find the max salary which is less than the result of sub query.

Pros:

Cons:

    • Using this method you can not find third highest salary. (You can find the third highest salary using sub query but your query become more complex at higher levels)

Method 2: Using DENSE_RANK Function (Why we are not using RANK function here? Check this in difference between RANK and DENSE_RANK)

In this give rank to each row on the basis of salary. Once done filter the required rank from the main result set.

Pros:

    • You can modify this query up to any level without increasing the complexity of the query.

Cons:

Solution: 

Prepare Data

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE TABLE #Employee
(
    EmployeeID INT IDENTITY(1,1),
    EmployeeName VARCHAR(100),
    Salary INT
);
 
INSERT INTO #Employee (EmployeeName,Salary) VALUES('Employee1',1298);
INSERT INTO #Employee (EmployeeName,Salary) VALUES('Employee2',1298);
INSERT INTO #Employee (EmployeeName,Salary) VALUES('Employee3',3478);
INSERT INTO #Employee (EmployeeName,Salary) VALUES('Employee4',3478);
INSERT INTO #Employee (EmployeeName,Salary) VALUES('Employee5',23234);
INSERT INTO #Employee (EmployeeName,Salary) VALUES('Employee6',12134);
INSERT INTO #Employee (EmployeeName,Salary) VALUES('Employee7',1231);
INSERT INTO #Employee (EmployeeName,Salary) VALUES('Employee8',3149);
INSERT INTO #Employee (EmployeeName,Salary) VALUES('Employee9',8954);
INSERT INTO #Employee (EmployeeName,Salary) VALUES('Employee10',3478);
INSERT INTO #Employee (EmployeeName,Salary) VALUES('Employee11',4839);
INSERT INTO #Employee (EmployeeName,Salary) VALUES('Employee12',3958);
INSERT INTO #Employee (EmployeeName,Salary) VALUES('Employee13',3471);
INSERT INTO #Employee (EmployeeName,Salary) VALUES('Employee14',23234);

Method 1

1
2
SELECT MAX(Salary) FROM #Employee
WHERE Salary < (SELECT MAX(Salary) FROM #Employee);

Method 2

1
2
SELECT * FROM (SELECT DENSE_RANK() OVER (ORDER BY Salary DESC) EmployeeSalaryRank,EmployeeName,Salary FROM #Employee) AS ResultSet
WHERE EmployeeSalaryRank = 2

 

Your comments, queries and suggestions are welcomed.