Click here to Skip to main content
15,882,114 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Dear Sir/Madam....
I want to get third maximum salary....
For that the given below query is working...
If we have more than one same salary(45000) means it will display last inserted value only.... I need to display all the 3rd maximum salary


SQL
CREATE TABLE Employee([Eid] [int] NULL,[Ename] [nvarchar](255) NULL,[Basic_Sal] [float] NULL)

insert into Employee values(1,'Neeraj',45000)
insert into Employee values(2,'Ankit',5000)
insert into Employee values(3,'Akshay',6000)
insert into Employee values(4,'Ramesh',7600)
insert into Employee values(5,'Vikas',4000)
insert into Employee values(7,'Neha',8500)
insert into Employee values(8,'Shivika',4500)
insert into Employee values(9,'Tarun',9500)
insert into Employee values(10,'meera',45000)
insert into Employee values(11,'kajal',45000)
insert into Employee values(12,'hansika',45000)

select Ename,Basic_sal
from(select Ename,Basic_Sal,ROW_NUMBER() over (order by Basic_Sal desc) as rowid from Employee)A
where rowid=3



Thanks in Advance
Posted

1 solution

Hi,

Use following query :

SQL
select Ename,Basic_sal, [rank]
from(select Ename,Basic_Sal,DENSE_RANK() over (order by Basic_Sal desc) as [rank] from Employee)A
where [rank]=3


Thanks,
Bhagyesh
 
Share this answer
 
Comments
M.Thiyagaraja 23-Apr-14 2:10am    
thank you so much sir/madam.....It's working fine....

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900