Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
lols
Posting Yak Master
174 Posts |
 Posted - 2009-03-28 : 02:55:57
|
I have an Enroll TableEnrollIDAgentIDEnrollmentDateI want to list how many Agents did how many Enrollments using the Rankfunction in SQL Server 05/08. How to do?Output needs to be :Position AgentId CountEnroll1 62 292 55 223 34 19 |
|
|
Nageswar9
Aged Yak Warrior
600 Posts |
Posted - 2009-03-28 : 02:59:08
|
| select * from ( select agentid,count(EnrollID) as countEnroll,row_number() over( partition by agentid order by agentid) as rn from urtable group by agentid) t |
 |
|
|
lols
Posting Yak Master
174 Posts |
Posted - 2009-03-28 : 03:14:22
|
| Thanks but I need to do this using the RANK function please |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-28 : 03:16:33
|
| [code]select agentid, countEnroll,,rank() over( partition by agentid order by countEnroll desc) as rnk from ( select agentid,count(EnrollID) as countEnroll from urtable group by agentid) t[/code] |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-28 : 03:19:24
|
| please note that using rank will not give you consecutive values always. suppose if you've two agents with count as 22 in above case then both of them will have rank of 2 however the next agent ( one with 19) will have a rank of 4 (as 3 agents have more count than him). so if you want continuos rank values irrespective of how many times a rank repeats, use dense_rank() instead of rank() in above solution. |
|
|
|
lols
Posting Yak Master
174 Posts |
Posted - 2009-03-28 : 03:26:45
|
| Thanks Visakh..I needed this only |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-28 : 03:32:51
|
welcome  Fine then... |
|
|
|
|
|
|
Using RANKfunction and gett resutls