Ranking by two fields

[Guest]

I am trying to rank players' performances by two different fields, for
instance if a player has 6 points, it then goes by his aggregate score. I
have managed to Rank the players by their points only, but I get a tie
between players. Is there a way I can Rank by 'PlayerPoints' and then by
'TotalAggregate'. I have posted my SQL which may make it more clear.

SELECT Average.Surname, Average.PlayerPoints, (Select Count(*) from
qryAverage Where [PlayerPoints] >[Average].[PlayerPoints])+1 AS Ranking,
Average.TotalAggregate
FROM qryAverage AS Average
ORDER BY Average.PlayerPoints DESC, Average.TotalAggregate DESC;


Surname PlayerPoints Ranking TotalAggregate
DALLAWAY 6 1 32
SMITH 6 1 25
HASTILOW 4 3 19
HOPSON 4 3 16
BOWATER 4 3 0
CHURMS 4 3 -2
TIMMINS 2 7 2

Any help would be extremely useful!! Thanks.

 

[Marshall Barton]

I am trying to rank players' performances by two different fields, for
instance if a player has 6 points, it then goes by his aggregate score. I
have managed to Rank the players by their points only, but I get a tie
between players. Is there a way I can Rank by 'PlayerPoints' and then by
'TotalAggregate'. I have posted my SQL which may make it more clear.

SELECT Average.Surname, Average.PlayerPoints, (Select Count(*) from
qryAverage Where [PlayerPoints] >[Average].[PlayerPoints])+1 AS Ranking,
Average.TotalAggregate
FROM qryAverage AS Average
ORDER BY Average.PlayerPoints DESC, Average.TotalAggregate DESC;


Surname PlayerPoints Ranking TotalAggregate
DALLAWAY 6 1 32
SMITH 6 1 25
HASTILOW 4 3 19
HOPSON 4 3 16
BOWATER 4 3 0
CHURMS 4 3 -2
TIMMINS 2 7 2

Try this subquery instead:

(SELECT Count(*)
FROM qryAverage As X
WHERE X.PlayerPoints > Average.PlayerPoints
OR (X.PlayerPoints = Average.PlayerPoints
AND X.TotalAggregate > Average.TotalAggregate)
)+1 AS Ranking

 

[Tom Ellison]

Dear Neil:

To me, it looks like:

SELECT Surname, PlayerPoints,
(SELECT Count(*)
FROM qryAverage A1
WHERE A1.PlayerPoints > A.PlayerPoints
OR (A1.PlayerPoints = A.PlayerPoints
AND A1.TotalAggregate > A.TotalAggregate) + 1
AS Ranking,
TotalAggregate
FROM qryAverage AS A
ORDER BY PlayerPoints DESC, TotalAggregate DESC;

How is this?

Tom Ellison

 

[Guest]

You guys are absolute legends!! Thanks Very Much!!!

Dear Neil:

To me, it looks like:

SELECT Surname, PlayerPoints,
(SELECT Count(*)
FROM qryAverage A1
WHERE A1.PlayerPoints > A.PlayerPoints
OR (A1.PlayerPoints = A.PlayerPoints
AND A1.TotalAggregate > A.TotalAggregate) + 1
AS Ranking,
TotalAggregate
FROM qryAverage AS A
ORDER BY PlayerPoints DESC, TotalAggregate DESC;

How is this?

Tom Ellison

I am trying to rank players' performances by two different fields, for
instance if a player has 6 points, it then goes by his aggregate score. I
have managed to Rank the players by their points only, but I get a tie
between players. Is there a way I can Rank by 'PlayerPoints' and then by
'TotalAggregate'. I have posted my SQL which may make it more clear.

SELECT Average.Surname, Average.PlayerPoints, (Select Count(*) from
qryAverage Where [PlayerPoints] >[Average].[PlayerPoints])+1 AS Ranking,
Average.TotalAggregate
FROM qryAverage AS Average
ORDER BY Average.PlayerPoints DESC, Average.TotalAggregate DESC;


Surname PlayerPoints Ranking TotalAggregate
DALLAWAY 6 1 32
SMITH 6 1 25
HASTILOW 4 3 19
HOPSON 4 3 16
BOWATER 4 3 0
CHURMS 4 3 -2
TIMMINS 2 7 2

Any help would be extremely useful!! Thanks.