assign ranks to top employee

Database

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 (1)
2 REPLIES
Enthusiast

Re: assign ranks to top employee

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,

Re: assign ranks to top employee

Thanks Raja for spending time on this. Really appreciated