Find 3rd highest salary in sql Or SQL query to find third highest salary in SQL Or Find the 3rd or Nth Highest/Lowest Salary in SQL Server

CREATE TABLE #Employee

(

       EmpID int IDENTITY(1,1) NOT NULL,

       Name varchar(50) NULL,

       Salary decimal(10, 2) NULL,

       Designation varchar(50) NULL

)

insert into #Employee(Name,Salary,Designation)

values('Yuvraj','20000.00','Trainee Software Engineer')

insert into #Employee(Name,Salary,Designation)

values('Virender','25000.00','Software Engineer')

insert into #Employee(Name,Salary,Designation)

values('Zaheer','30000.00','Senior Software Engineer')

insert into #Employee(Name,Salary,Designation)

values('Gautam','35000.00','Team Leader')

insert into #Employee(Name,Salary,Designation)

values('Harbhajan','40000.00','Team Leader')

insert into #Employee(Name,Salary,Designation)

values('Bhuvneshwar','45000.00','Project Cunsultant')

insert into #Employee(Name,Salary,Designation)

values('Varun ','50000.00','Project Manager')

select * from #Employee

Results

EmpID       Name            Salary    Designation

---------------------------------------------------------------       

1           Yuvraj          20000.00  Trainee Software Engineer

2           Virender        25000.00  Software Engineer

3           Zaheer          30000.00  Senior Software Engineer

4           Gautam          35000.00  Team Leader

5           Harbhajan       40000.00  Team Leader

6           Bhuvneshwar     45000.00  Project Cunsultant

7           Varun           50000.00  Project Manager

(7 row(s) affected)

QUERY TO GET 3RD HIGHEST SALARY

SELECT MIN(Salary)'3rd Highest Salary'

FROM (SELECT DISTINCT TOP 3 Salary FROM #Employee ORDER BY Salary DESC) E

Results

3rd Highest Salary

---------------------------------------

40000.00

(1 row(s) affected)

QUERY TO GET 3RD LOWEST SALARY

SELECT MAX(Salary)'3rd Lowest Salary'

FROM (SELECT DISTINCT TOP 3 Salary FROM #Employee ORDER BY Salary ASC) E

Results

3rd Lowest Salary

---------------------------------------

30000.00

(1 row(s) affected)

/*******************************************************************/

/***************Query to get nth (3rd) Highest Salary***************/

/*******************************************************************/

USING ROW NUMBER

SELECT Salary'3rd Highest Salary'

FROM

  (

          SELECT Salary,Name,ROW_NUMBER() OVER(ORDER BY Salary DESC) As RowNum

          FROM #EMPLOYEE

  ) As A

WHERE A.RowNum IN (3)

Results

3rd Highest Salary

---------------------------------------

40000.00

(1 row(s) affected)

USING TOP KEYWORD

Select TOP 1 Salary'3rd Highest Salary'

from (SELECT DISTINCT TOP 3 Salary from #Employee ORDER BY Salary DESC)

a ORDER BY Salary ASC

Results

3rd Highest Salary

---------------------------------------

40000.00

(1 row(s) affected)

USING SUB QUERY

SELECT Salary'3rd Highest Salary'

FROM #Employee Emp1

WHERE 3 = (

SELECT COUNT(DISTINCT(Emp2.Salary))

FROM #Employee Emp2

WHERE Emp2.Salary >= Emp1.Salary)

Results

3rd Highest Salary

---------------------------------------

40000.00

(1 row(s) affected)

USING OFFSET

select Salary'3rd Highest Salary'

from #Employee

ORDER BY Salary DESC

OFFSET  3-1 ROWS

FETCH NEXT 1 ROWS ONLY

Results

3rd Highest Salary

---------------------------------------

40000.00

(1 row(s) affected)

/******************************************************************/

/***************Query to get nth (3rd) Lowest Salary***************/

/******************************************************************/

USING ROW NUMBER

SELECT Salary'3rd Lowest Salary'

FROM

  (

          SELECT Salary,Name,ROW_NUMBER() OVER(ORDER BY Salary ASC) As RowNum

          FROM #EMPLOYEE

  ) As A

WHERE A.RowNum IN (3)

Results

3rd Lowest Salary

---------------------------------------

30000.00

(1 row(s) affected)

USING TOP KEYWORD

Select TOP 1 Salary'3rd Lowest Salary'

from (SELECT DISTINCT TOP 3 Salary from #Employee ORDER BY Salary ASC)

a ORDER BY Salary DESC

Results

3rd Lowest Salary

---------------------------------------

30000.00

(1 row(s) affected)

USING SUB QUERY

SELECT Salary'3rd Lowest Salary'

FROM #Employee Emp1

WHERE 3 = (

SELECT COUNT(DISTINCT(Emp2.Salary))

FROM #Employee Emp2

WHERE Emp2.Salary <= Emp1.Salary)

Results

3rd Lowest Salary

---------------------------------------

30000.00

(1 row(s) affected)

USING OFFSET

select Salary'3rd Lowest Salary'

from #EMPLOYEE

ORDER  BY Salary ASC

OFFSET  3-1 ROWS

FETCH NEXT 1 ROWS ONLY

Results

3rd Lowest Salary

---------------------------------------

30000.00

(1 row(s) affected)

Note:-

Simply you can change the highlighted number to desired number to get that position highest/lowest salary.