Different way to find 2nd minimum Salary TIP #36

 

Mostly people faced this question in interview find highest salary so I am just trying to explain different ways

let us understand this by following example

suppose we have following table of employees with salary

Query 1:-  by  aggregate function

SELECT MAX (fltBasicSalary)
FROM @tblEmployeeSalary
WHERE fltBasicSalary IN (SELECT DISTINCT TOP 2 fltBasicSalary
FROM @tblEmployeeSalary
ORDER BY fltBasicSalary ASC)

Query 2:- by Inline Query

SELECT MIN(fltBasicSalary)
FROM @tblEmployeeSalary e1
WHERE 2 <=(SELECT COUNT(*)
FROM @tblEmployeeSalary e2 WHERE e1.fltBasicSalary >= e2.fltBasicSalary);

Query 3:- BY Row_Number()

SELECT tmp.fltBasicSalary
FROM (SELECT DISTINCT fltBasicSalary ,
ROW_NUMBER()OVER (ORDER BY fltBasicSalary ASC) As intRowNumber
FROM @tblEmployeeSalary)tmp
WHERE tmp.intRowNumber = 2

Query 4:-  BY Rank()

SELECT tmp.fltBasicSalary
FROM (SELECT DISTINCT fltBasicSalary ,
RANK()OVER (ORDER BY fltBasicSalary ASC) As intRowNumber
FROM @tblEmployeeSalary)tmp
WHERE tmp.intRowNumber = 2

Query 5:- Group by  & Sub query

SELECT TOP 1 fltBasicSalary
FROM (SELECT TOP 2 fltBasicSalary
FROM @tblEmployeeSalary
GROUP BY fltBasicSalary ORDER BY fltBasicSalary ASC) AS tmp ORDER BY fltBasicSalary DESC

I hope you like it.

Enjoy !!!

RJ