rank in select query not right

  • Thread starter Thread starter pat67
  • Start date Start date
P

pat67

I have a query i am using to rank plates based on winning pct. The
problem i have is i guess since i am doing select count, it is countin
the players at the pct and ranking based on count. query is this

SELECT (SELECT COUNT(*)
FROM qryPlayers_On_Target as t2
WHERE t2.Pct > t1.Pct OR t2.Pct = t1.Pct AND t2.Won >= t1.Won) AS
Rank, t1.PlayerName, t1.Team, t1.GP, t1.Won, t1.Lost, t1.Pct
FROM qryPlayers_On_Target AS t1
ORDER BY t1.Pct DESC , t1.Won DESC;

problem like i said is it is the first week and 5 players are 3 and 0
so all 5 have the rank of 5. the next 9 are 2 and 1 and all have rank
of 14. this needs to be the first 5 with a rank of 1 and the next 9
rank of 6. any ideas would be great. Thanks
 
I have a query i am using to rank plates based on winning pct. The
problem i have is i guess since i am doing select count, it is countin
the players at the pct and ranking based on count. query is this

SELECT (SELECT COUNT(*)
FROM qryPlayers_On_Target as t2
WHERE t2.Pct > t1.Pct OR t2.Pct = t1.Pct AND t2.Won >= t1.Won) AS
Rank, t1.PlayerName, t1.Team, t1.GP, t1.Won, t1.Lost, t1.Pct
FROM qryPlayers_On_Target AS t1
ORDER BY t1.Pct DESC , t1.Won DESC;

problem like i said is it is the first week and 5 players are 3 and 0
so all 5 have the rank of 5. the next 9 are 2 and 1 and all have rank
of 14. this needs to be the first 5 with a rank of 1 and the next 9
rank of 6. any ideas would be great. Thanks
Click to expand...

i fixed this. thanks
 
Back
Top