Hi folks,
I'm having trouble trying to rank each of our students, by the homegroup they are in.
Example fake data.
HomeGroup
1. Team RED
2. Team BLUE
3. Team Skeet
4. Team GREEN
Students
1. John, Score - 34, Team RED
2. Jill, Score - 87.3, Team RED
3. Fred, Score - 41, Team GREEN
4. Jane, Score 93, Team BLUE
...
etc.
My output is in the following
Scores
StudentId 1, Score 34, Rank - 5th
StudentId 2, Score 87.3, Rank - 1st
StudentId 3, Score 41, Rank - 9th
StudentId 4, Score 94, Rank - 1st.
I'm trying to use DENSE_RANK .. and that works fine if i run this against a SINGLE HomeGroup (eg. homegroup = 1)... but i'm not sure how to do this over ALL the homegroups.
I'm guessing i need an Update query, which has a subquery ... and this subquery has it's own subquery ... ??
I'm pretty confused :( And i keep thinking about evil cursors which i refuse to use unless i've got a gun to my head (cause i believe it can be handled correctly with 'Set' notation).