Find Nth Highest Record

Problem: Show the list of all employees from the employee table having second highest Salary or Nth highest salary in the organization.

Solution: We can achieve the result by so many ways.

Run the below query to create the table in your database and to insert data into it.

CREATE TABLE [dbo].[employee](

         [slNo] [INT] NULL,

         [empname] [VARCHAR](50) NULL,

         [salary] [FLOAT] NULL

)

INSERT INTO [dbo].[employee]([slNo],[empname],[salary])

SELECT '101', 'Ram', '5000' UNION All

SELECT '102', 'Abhishek', '7500' UNION All

SELECT '101', 'kumar', '5000' UNION All

SELECT '104', 'prasad', '6570' UNION All

SELECT '102', 'Jumla', '7500' UNION All

SELECT '101', 'Harkesh', '12000' UNION All

SELECT '101', 'John', '4000'

First:by subquery

Select  Top 1 salary

From (

select top 2 salary

from employee

order by salary desc) a

Order by salary

Second: using the aggregate function

Select  min(salary) from employee

Where salary in (select top 2 salary

from employee

order by salary desc)

Third:Using Dense rank

SELECT * FROM (

  SELECT DENSE_RANK() OVER(ORDER BY salary DESC) AS RankID, * FROM dbo.Employee ) InnQ              WHERE InnQ.RankID = 2

Below query only for getting 2^nd highest salary:

SELECT * FROM dbo.Employee WHERE salary = 

         (

                 SELECT MAX(salary) FROM dbo.Employee WHERE salary < 

                 (SELECT MAX(salary) FROM dbo.Employee)

         )

SELECT * FROM dbo.Employee WHERE salary = 

         (        

                 SELECT MAX(salary) FROM dbo.Employee WHERE salary NOT IN 

                 (SELECT MAX(salary) FROM dbo.Employee)

         )