ranking by group

[aspnet98]

I have a view that returns data in the following way:
GroupID Username Week W L T TBD
1070 jay 1 10 3 0 12
1070 jay 2 11 3 0 1
1070 john 1 14 2 0 15
1070 john 2 15 1 0 19

There are multiple GroupIDs.
Their are multiple Weeks.

I need to set a rank in sql by adding another field to this query called rank.

The rank should be based on most wins and smallest TBD.

If their is a tie for most wins, the smallest TBD decides the winner. If that ties also, then they both get a number 1 rank and so on down the chain.

rank username wins tbd
1 john 10 1
1 jason 10 1
2 mark 8 20
3 mat 4 12
3 kevin 4 12

The issue is that I need to do this for every groupid and every week. So the above eaxple was for group id 1000 and week 1. I now need to rank the next group for week 1 and so on...

Is this possible?

 

[ClayG]

I think you can do this

Select GroupID, Week, Username,
Wins, TBD,
( Select Count(*)
from Results R2
where R2.GroupID = R1.GroupID
and R2.Week = R1.Week
and ( R2.Wins < R1.Wins
or R2.Wins = R1.Wins
and R2.TBD > R1.TBD )
) + 1 as Ranking
from Results R1

 

[vongrunt]

If this problem is about sports/competitive standings, shouldn't tie critera apply in this order:

1) Wins-Losses (max)
2) Wins (max)
3) TBD (min)

Unless all matches from one round are always scheduled within a week... but in that case W + L would be constant for all usernames within same GroupID and Week. And that is not the case with sample data you gave (jay has W+L=10+3=13, john has 14+2=16 for GroupID=1070 and Week=1).

?

Also: some sources prefer to assign rankings this way:

1 1 3 4 4 6

... instead of

1 1 2 3 3 4

Which one is correct, we can only agree to disagree... so pick one .

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[aspnet98]

von
1) Wins (max)
2) TBD (min)


and

1 1 2 3 3 4

Is how my mini standings db is structured.

Each username may have different amounts of games played (or chances to get wins and losses). I would use a percentage to determine those winners.

 

[aspnet98]

clay:
Everyone gets a rank 1???

any other ideas?

 

[aspnet98]

clay, I amsorry I picked a week with no data.

I did try it on a week with data and it produced the following.

it gave the username with the most wins the highest rank but it was not number 1. It was the opposite. The username with the least wins got a number 1.

I need it to be the other way around.

also it did not number the rank correctly.

john 1
jay 1
mason 1
kevin 4 ???? This should read 2 because rank 1 tied three ways.

 

[aspnet98]

clay, if you could correct the order of the ranks 1 being most wins then add another column called ranking2 that would counter on the 1,1,1,1,5,6,7 and make it read 1,1,1,1,2,3,4.....

you have the right idea though. can we try to fix that?

it is a massive table, will performance be slow? what is the most efficient way to approach this?

 

[vongrunt]

This is hard to pull out without heavy redundancy in query or temp table. Unless...

select S1.GroupID, S1.Week,  S1.Username, S1.Wins, S1.TBD, count(distinct S2.rankorder) as rank
from standings S1
inner join 
(	select GroupID, Week, Username, 1000*Wins+(1000-TBD) as rankorder
	from standings
) S2 on S1.GroupID=S2.GroupID and S1.Week=S2.Week
where 1000*S1.Wins+(1000-S1.TBD) >= S2.rankorder
group by S1.GroupID, S1.Week, S1.Username, S1.Wins, S1.TBD
order by S1.GroupID, S1.Week, rank

The trick is to make single ranking expression without any data loss (in code above I assumed Wins and TBD are always between [0, 999]) - and then perform distinct rank.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[aspnet98]

hey von,
It orders the username with the most wins as the highest rank. That username needs to get a rank of 1. The person with the least amount of wins (smallest TBD) gets the higest rank.

Can you reverse the ranking order?

 

[aspnet98]

Von:
To add to my last post, the usernames with 0 wins all get a 1 rank. They would need to be rank highest (example rank 100 ou of 100 usernames)...

Hope this helps and thank you for helping me on this.

 

[aspnet98]

just another thought. What if I made your 1000*Wins a negative.

-1000*Wins

Would that reverse the rank order?

 

[vongrunt]

Ack... more wins, less TBD, wins have precedence over TBD:

1000*(1000-Wins)+TBD

Try this:

select S1.GroupID, S1.Week,  S1.Username, S1.Wins, S1.TBD, count(distinct S2.rankorder) as rank
from standings S1
inner join 
(    select GroupID, Week, Username, 1000*(1000-Wins)+TBD as rankorder
    from standings
) S2 on S1.GroupID=S2.GroupID and S1.Week=S2.Week
where 1000*(1000-S1.Wins)+S1.TBD >= S2.rankorder
group by S1.GroupID, S1.Week, S1.Username, S1.Wins, S1.TBD
order by S1.GroupID, S1.Week, rank

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[aspnet98]

ok great. one last thing. i need to alter the version again to make it rank like this.
rank
1
1
3
4
4
6

The problems is the rules of this thing. I may not add up to the total usernames in the group if I do not have a version like the one I described above.

Can I simply remove the distinct count?

Sorry to be a bother, I am learning a lot.

 

[aspnet98]

oh yea, von i should have listened to your first post on this regarding the ranking order.

i am simply stupid.......

 

[vongrunt]

Then code ClayG posted should be OK, just sort results and see for yourself:

Select GroupID, Week, Username,
    Wins, TBD,
    ( Select Count(*)
        from Standings R2
        where R2.GroupID = R1.GroupID
          and R2.Week = R1.Week
          and ( R2.Wins < R1.Wins 
            or R2.Wins = R1.Wins 
              and R2.TBD > R1.TBD )
      ) + 1 as Ranking
  from Standings  R1
[b]order by GroupID, Week, ranking[/b]

Yup, such queries are sometimes tricky and not always efficient... that's one of reasons why some people (including myself) preach about client-side ranking over ordered results. This will change with SQL2005 (Yukon) but until then...

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[aspnet98]

this is so tricky.........

usings clays version:
it gave the username with the most wins the highest rank but it was not number 1. It was the opposite. The username with the least wins got a number 1.

I need it to be the other way around.

can we flip it?

 

[vongrunt]

Unless I missed something... in above query replace '<' with '>' and vice versa and try again.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[aspnet98]

Von,
Sorry for the delayed response. I was sick until yesterday than had more internet problems when i tried to post to threds. argh.

on a hppy note clays code and an adaptation of yours worked out perfectly.

i appreciate all your help. i will definitly be picking your brain some more because I am starting to have some performace issues with some other queries i developed. resolving those is probably to advanced at this stage of my learning curver....