I have created a table and inserted some records in Table for understanding purposes.
— Creating New Table
create table Employee
(
ID int identity,
Name varchar(20),
Salary float,
Dept_Name varchar(20)
)
— Inserting data in table
Insert into Employee(Name,Salary,Dept_Name)values(‘Alice’,20000,’HR’)
Insert into Employee(Name,Salary,Dept_Name)values(‘Bob’,10000,’Finance’)
Insert into Employee(Name,Salary,Dept_Name)values(‘Camlie’,28000,’IT’)
Insert into Employee(Name,Salary,Dept_Name)values(‘Don’,15000,’Sales’)
Insert into Employee(Name,Salary,Dept_Name)values(‘Elli’,39000,’HR’)
Insert into Employee(Name,Salary,Dept_Name)values(‘Fukale’,12000,’Finance’)
Insert into Employee(Name,Salary,Dept_Name)values(‘Govin’,40000,’IT’)
Insert into Employee(Name,Salary,Dept_Name)values(‘Halios’,32000,’Finance’)
Insert into Employee(Name,Salary,Dept_Name)values(‘Idomnes’,56000,’IT’)
Insert into Employee(Name,Salary,Dept_Name)values(‘Jacob’,29000,’Communication’)
— Populate the data through Select Statement
Select * from Employee order by salary desc
—-Now we will find the 2nd or 3rd Or Nth highest salary in employee table through below Query
SELECT *
FROM Employee em1
WHERE 2 = (
SELECT COUNT( DISTINCT ( em2.Salary ) )
FROM Employee em2
WHERE em2.Salary >= em1.Salary
)
Find the output below:-
