How to select top 2 salary and name from employee table? [Resolved]

Posted by Rajendra.prasad under Sql Server on 5/3/2011 | Points: 10 | Views : 29896 | Status : [Member] | Replies : 7
Hi All,

How to select top 2 salary and name from employee table?

Regards,
Raj




Responses

Posted by: Ndebata on: 5/3/2011 [Member] Starter | Points: 50

Up
0
Down

Resolved
Hi
lets say Table Employee has two Columns

EmployeeName | Salary
ABC | 5000
XYZ | 5000
PQR | 4000
DOT | 4000
NET | 3000


In order to find out the Employee(s) and salary who are getting highest salary, the query should return two ABC and XYZ

if you want to find out the Employee(s) who are getting top 2 salarys then it should return ABC,XYZ,PQR,DOT

There are several ways you can get this.
1. Use of DENSE_RANK() T-SQL Function,
The bellow query list out employees who are getting highest salary

SELECT EmployeeName,Salary from
(SELECT *,DENSE_RANK() over( order by Salary desc) SRANK from Employee) as Emp
Where SRANK = 1

If you want who are getting 2nd highest salary you can just change the where clause

Where SRANK = 2

in your case you want to list out two top most salary paid employees so you can use
  Where SRANK <= 2 

To know more about DENSE_RANK() you can refer this msdn link
http://msdn.microsoft.com/en-us/library/ms173825.aspx
2. You can achieve the same using inner query also

SELECT EmployeeName,Salary from
(Select *,(Select Count(1)+1 from (select Salary from Employee group by Salary)as xyz where xyz.Salary>e.Salary ) SRANK
from Employee e) as Emp
Where SRANK<=2

@Dear PandianS ,please check your query may not work in case more than one persons are getting highest salary(Sample table listed above) :)
Thanks,
Debata

Rajendra.prasad, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vforvijay on: 5/3/2011 [Member] Starter | Points: 25

Up
0
Down
hi..
Try this one

select top 2 sal from emp order by name


http://vbdotnetaddict.blogspot.com

Rajendra.prasad, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sault on: 5/3/2011 [Member] Starter | Points: 25

Up
0
Down
select top 2 name, salary from employee order by name



Rajendra.prasad, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: PandianS on: 5/3/2011 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi

To findout 1st highest salary with name:
Select a.Name,a.Salary  from Tb_Samples a

where 1= (Select count(1) From Tb_Samples b
where b.Salary >=a.Salary)
Go
To findout highest 2 salaries with name:
Select Top 2 Name,Salary from Tb_Samples Order by Salary Desc

Go
Cheers

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Rajendra.prasad, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bugwee on: 5/3/2011 [Member] Starter | Points: 25

Up
0
Down
you may try this one

select top 2 salary,name from emp order by salary desc




Rajendra.prasad, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Prafullshrivastava on: 5/4/2011 [Member] Starter | Points: 25

Up
0
Down
try this :-

select top 2 name,sal from employee order by sal desc

i think it is helpful to you............

Pshrivastava

Rajendra.prasad, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriramnandha on: 11/24/2011 [Member] Starter | Points: 25

Up
0
Down
select Top 2 empsalary from employee order by empid



sriram

Rajendra.prasad, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response