Find the Highest,2nd Highest,Nth Highest Salary in SQL SERVER

Hello,

Below is the complete SQL QUERY to find the Highest Salary and 2nd Highest salary & Nth highest salary.




CREATE DATABASE MY_PRACTICE

GO

USE MY_PRACTICE

GO
CREATE TABLE [dbo].[persons](
[id] [int] IDENTITY(1,1) PRIMARY KEY,
[Name] [char](20) NULL,
[age] [int] NULL,
[salary] [float] NULL)

GO

INSERT INTO persons VALUES ('sagar',28,5000),('shirish',25,2000),('raj',32,4000),('AMBI',27,8000)
,('SURESH',30,6000)

go
--Employee details
select * from dbo.persons
go

-- Find the highest salary of the employee
select name,MAX(salary) from persons
group by Name

go
-- Find the 2nd highest salary of the employee
select id,name,max(salary) as second_high_salary from persons
where salary <> (select MAX(salary)from persons)
group by id,Name order by second_high_salary desc

go
-- Find the Nth highest salary for the Employee
select * from persons P1
WHERE (1) = (SELECT COUNT(DISTINCT(P2.salary)) FROM persons P2 WHERE P2.salary > P1.salary )

-- USING TOP KEYWORD FIND THE NTH HIGHEST SALARY

SELECT TOP 1 SALARY
FROM (SELECT DISTINCT TOP 1 SALARY FROM persons
ORDER BY salary DESC)
AS EMP
ORDER BY salary