Want to print 3rd highest salary and name along with it in SQL server table?-CodePudding

Create table tbl_Employees  
(  
     Id int primary key not null identity(1,1),  
     FirstName varchar(50),  
     LastName varchar(20),  
     Location varchar(20),  
     Gender varchar(50),  
     Salary int,
     Age int
)  

Insert into tbl_Employees values ('Chittaranjan', 'Swain','Odisha', 'Male', 80000,23)  
Insert into tbl_Employees values ('Chandin', 'Swain', 'Pune','Female', 76000,34)  
Insert into tbl_Employees values ('Mitu', 'Pradhan','Delhi', 'Male', 55000,20)  
Insert into tbl_Employees values ('Jeni', 'Swain','Chennai', 'Female', 76000,34)  
Insert into tbl_Employees values ('Adyashree', 'Swain','UK', 'Female', 49000,36)  
Insert into tbl_Employees values ('Ram', 'Kumar','US', 'Male', 39000,45)  
Insert into tbl_Employees values ('Jitendra', 'Gouad','Hydrabad', 'Male', 35000,35)  
Insert into tbl_Employees values ('Dibas', 'Hembram','Bangalore', 'Male', 55000,56)

I want to find out the 3rd highest salary and wants to print it. But along with that, i also want to print the name of that person

select min(Salary) as minimum
from
    (select distinct TOP 3 Salary
    From tbl_Employees
    order by Salary desc
    ) 
as a

I have been able to find out the 3rd highest salary only till now, but i want name of that particular person with it also.

CodePudding user response：

The window function row_number() over() should do the trick

Note: Use dense_rank() if you want to see ties.

Select *
 From  (
         Select *
               ,RN = row_number() over (order by salary desc)
             From tbl_Employees  
       ) A
 Where RN = 3

CodePudding user response：

OFFSET seems to be what you want here:

SELECT FirstName,
       LastName,
       Salary
FROM dbo.tbl_Employees E
ORDER BY Salary DESC
OFFSET 2 ROWS FETCH NEXT 1 ROW ONLY;