All Forums Database
13 Jan 2014
assign ranks to top employee

Hi I'm new to Teradata, can you please let me how to achive this requirement
 
I have to assign rank to employee who performed well in all departments. Each employee has 3 departments (A,B,C) and the business they did for the orginazation
Input data
Employee name, Dept Name, Amount
1,A,20
1,B,20
1,C,15
2,B,20
2,C,15
2,A,15
3,A,15
3,B,30
3,C,15
out of 3 employees, #3 did good business of $60, next #1 then #2
so, I have assign ranks like below
Employee name, Dept Name, Amount, Rank
1,A,20,2
1,B,20,2
1,C,15,2
2,B,20,3
2,C,15,3
2,A,15,3
3,A,15,1
3,B,30,1
3,C,15,1
Please help me how to achive this?
Thanks
Sree

Tags:
Raja_KT 1246 posts Joined 07/09
14 Jan 2014

Hi Srikanth,

select b.name,b.dept,b.amount,a.rnk from 

(select name,sum(amount) as sumamt, rank() over (order by sumamt desc) rnk

from db1.raja_test 

group by 1) a,

(select name,dept,amount from db1.raja_test  ) b

where a.name=b.name

order by 1

 

Cheers,

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

14 Jan 2014

Thanks Raja for spending time on this. Really appreciated

You must sign in to leave a comment.