How to select top 2 salary and name from employee table? [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

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

select top 2 name, salary from employee order by name

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

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

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

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

select Top 2 empsalary from employee order by empid

sriram

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