help with ranking in a query

[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?

 

[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);

 

[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