help with ranking in a query

P

pat67

I have the followign query that ranks players based on winning pct and
wins.

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

What I need to do is also rank by the minimum required games. I have a
filed called MinGames as well as GP. The way I need it to work is if
say the minimum is 5 games played. A player that is say 4-0 does not
have the minimum games played so he is not ranked first. he is ranked
as the highest player once all the players who have played at least 5
games are all ranked. so if there are 40 players with the minimum, the
player that is 4-0 is ranked 41st. I tried adding t1.GP>t1.MinGames in
the count, but that just didn't work. any ideas?
 
P

pat67

I have the followign query that ranks players based on winning pct and
wins.

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

What I need to do is also rank by the minimum required games. I have a
filed called MinGames as well as GP. The way I need it to work is if
say the minimum is 5 games played. A player that is say 4-0 does not
have the minimum games played so he is not ranked first. he is ranked
as the highest player once all the players who have played at least 5
games are all ranked. so if there are 40 players with the minimum, the
player that is 4-0 is ranked 41st. I tried adding t1.GP>t1.MinGames in
the count, but that just didn't work. any ideas?



I tried a new query where i am combining two queries, one for
qualified and one for not qualified. I am not doing it correctly
because it isn't working. Here is the query. Any ideas?


SELECT ((SELECT COUNT(*)
FROM qryPlayers_On_Target as t2
WHERE t2.Pct > t1.Pct OR t2.Pct = t1.Pct AND t2.Won > t1.Won)+1 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
SELECT (SELECT COUNT(*)
FROM qryPlayers_Not_On_Target_1 as t2
WHERE t2.Pct > t1.Pct OR t2.Pct = t1.Pct AND t2.Won > t1.Won)+1 AS
Rank, t1.PlayerName, t1.Team, t1.GP, t1.Won, t1.Lost, t1.Pct
FROM qryCount_Of_Matches, qryPlayers_Not_On_Target_1 AS t1
ORDER BY t1.Pct DESC , t1.Lost);
 
P

pat67

I have the followign query that ranks players based on winning pct and
wins.

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

What I need to do is also rank by the minimum required games. I have a
filed called MinGames as well as GP. The way I need it to work is if
say the minimum is 5 games played. A player that is say 4-0 does not
have the minimum games played so he is not ranked first. he is ranked
as the highest player once all the players who have played at least 5
games are all ranked. so if there are 40 players with the minimum, the
player that is 4-0 is ranked 41st. I tried adding t1.GP>t1.MinGames in
the count, but that just didn't work. any ideas?





I have tried a union query from two queries. one for qualified and one
for not. The issue i have is the non-qualified starts with 1 again. I
need it to start where the first part left off. here is the query

SELECT (SELECT COUNT(*)
FROM qryPlayers_On_Target as t2
WHERE t2.Pct > t1.Pct OR t2.Pct = t1.Pct AND t2.Won > t1.Won)+1 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
UNION ALL SELECT (SELECT COUNT(*)
FROM qryPlayers_Not_On_Target_1 as t2
WHERE t2.Pct > t1.Pct OR t2.Pct = t1.Pct AND t2.Won > t1.Won)+1 AS
Rank, t1.PlayerName, t1.Team, t1.GP, t1.Won, t1.Lost, t1.Pct
FROM qryCount_Of_Matches, qryPlayers_Not_On_Target_1 AS t1
ORDER BY t1.Pct DESC , t1.Lost;

Any ideas let me know
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top