ranking players in a competition

J

JOSELUIS

HI MY NAME IS JOSE LUIS FROM SPAIN I´M A ROCKIE IN ACCESS.I CREATE A ACCESS
DATABASE FOR A SPORT COMPETITION.
IN A QUERY I CREATE :
PLAYERID/PLAYERNAME/TEAM/MARK
BUT I NEED TO RANK ALL THE PLAYERS IN EACH TEAM.
WHY? BECAUSE ONLY THE THREE BEST PLAYERS IN EACH TEAM ARE TAKING INTO ACCOUNT
IN THE TEAM CLASIFICATION THEREFORE THE PLAYER WHOSE MARK IS HIGHER WILL BE
1º AND SO ON UPTO 3º AND TEAMMARK= SUM(MARK) IF RANKING= 1º,2º,3º
SORRY FOR MY ENGLISH AND THANK YOU IN ANTICIPATION.
 
K

KenSheridan via AccessMonster.com

Jose Luis:

The following query, based on your existing query, should do it:

SELECT Team, PlayerID, PlayerName, Mark,
(SELECT COUNT(*)+1
FROM YourFirstQuery AS Q2
WHERE Q2.Team = Q1.Team
AND Q2.Mark > Q1.Mark) AS Rank
FROM YourFirstQuery AS Q1
ORDER BY Team, Mark DESC, PlayerID, PlayerName;

You can then base another query on this second query to give you the team
marks:

SELECT Team, SUM(Mark) AS TeamMark
FROM YourSecondQuery
WHERE Rank <=3
GROUP BY Team;

In each case change the query and/or field names to your real ones, but you
don't need to change the Q1 or Q2 aliases.

Bear in mind that you could get more than 3 players per team ranked at 3 or
above if 2 or more tie for third place, 3 or more tie for second place or 4
or more tie for first place.

No need to apologise for your English; its far better than my meagre Spanish!
But please don't post in all capital letters; it sounds like you are shouting
and is not considered good 'netiquette'.

Ken Sheridan
Stafford, England
 
J

JOSELUIS via AccessMonster.com

KenSheridan said:
Jose Luis:

The following query, based on your existing query, should do it:

SELECT Team, PlayerID, PlayerName, Mark,
(SELECT COUNT(*)+1
FROM YourFirstQuery AS Q2
WHERE Q2.Team = Q1.Team
AND Q2.Mark > Q1.Mark) AS Rank
FROM YourFirstQuery AS Q1
ORDER BY Team, Mark DESC, PlayerID, PlayerName;

You can then base another query on this second query to give you the team
marks:

SELECT Team, SUM(Mark) AS TeamMark
FROM YourSecondQuery
WHERE Rank <=3
GROUP BY Team;

In each case change the query and/or field names to your real ones, but you
don't need to change the Q1 or Q2 aliases.

Bear in mind that you could get more than 3 players per team ranked at 3 or
above if 2 or more tie for third place, 3 or more tie for second place or 4
or more tie for first place.

No need to apologise for your English; its far better than my meagre Spanish!
But please don't post in all capital letters; it sounds like you are shouting
and is not considered good 'netiquette'.

Ken Sheridan
Stafford, England
Click to expand...
Best regards
Jose Luis,
Leon,Spain.
 
J

JOSELUIS via AccessMonster.com

I´m really sorry I made a mistake in the SQL but You´re right it works
perfectly but you get a good point now it can be that two or more players get
hte same ranking.Any ideas?
Jose Luis:

It shouldn't do that. Please post the SQL and name of your original query.

Ken Sheridan
Stafford, England
[quoted text clipped - 4 lines]
Jose Luis,
Leon,Spain.
Click to expand...
Click to expand...
 
K

KenSheridan via AccessMonster.com

Strictly speaking two players with the same mark are the same rank of course,
but you can give one an artificially higher rank than the other provided that
there is some way of uniquely distinguishing between them, which I assume
PlayerID does. So using this also in the subquery should do it:

SELECT Team, PlayerID, PlayerName, Mark,
(SELECT COUNT(*)+1
FROM YourFirstQuery AS Q2
WHERE Q2.Team = Q1.Team
AND Q2.Mark > Q1.Mark
AND Q2.PlayerID > Q1.PlayerID) AS Rank
FROM YourFirstQuery AS Q1
ORDER BY Team, Mark DESC, PlayerID, PlayerName;

Ken Sheridan
Stafford, England
I´m really sorry I made a mistake in the SQL but You´re right it works
perfectly but you get a good point now it can be that two or more players get
hte same ranking.Any ideas?
Jose Luis:
Click to expand...
[quoted text clipped - 8 lines]
Click to expand...
 
K

KenSheridan via AccessMonster.com

On second thoughts, that wouldn't work either. The only way I can think of
to distinguish between the players with the same marks and maintain the
ranking by mark otherwise, is to introduce a second subquery:

SELECT Team, PlayerID, PlayerName, Mark,
(SELECT COUNT(*)+1+
(SELECT COUNT(*)
FROM YourFirstQuery AS Q3
WHERE Q3.Team = Q1.Team
AND Q3.Mark = Q1.Mark
AND Q3.PlayerID > Q1.PlayerID)
FROM YourFirstQuery AS Q2
WHERE Q2.Team = Q1.Team
AND Q2.Mark > Q1.Mark)
AS Rank
FROM YourFirstQuery AS Q1
ORDER BY Team, Mark DESC, PlayerID DESC;

There may be a simpler solution, but if so, it escapes me.

Ken Sheridan
Stafford, England
 
Top