Thursday 30 May 2013

How to fetch the top four salaries without duplicate records ?

For example we have a Employee table like below, in this we need (nagarjuna,2000), (vijay,3000),(Ram,4000) and (bharani,5000) records.

 Employee Table Data :

NAME
SALARY
venkat   
1000
satya    
1000
nagarjuna
2000
jyothi   
2000
krishna  
2000
vijay    
3000
Anil     
3000
Ram      
4000
bharani  
5000


 Expected Result :

NAME
SALARY
bharani  
5000
Ram      
4000
Anil     
3000
jyothi   
2000

Query to fetch the expected result,

SELECT TOP 4 salary, 
             name 
FROM   (SELECT salary, 
               name, 
               Row_number() 
                 OVER( 
                   partition BY salary 
                   ORDER BY salary DESC) AS Record_count 
        FROM   employee 
        GROUP  BY salary, 
                  name) e 
WHERE  record_count = 1 
ORDER  BY salary DESC 

No comments:

Post a Comment