Rank question

[cinnie]

Greetings to all - I have written SQL to rank athlete's scores. My query
works, but not quite the way I'd like.

Here is a simplified version of my tblAthScores:
AthID AthScore
102 23.6
103 19.5
123 21.9
177 22.4
179 21.9
215 18.4

My SQL is:
SELECT tblAthScores.AthID, tblAthScores.AthScore,
(SELECT Count(*)
FROM tblAthScores AS Copy
WHERE Copy.AthScore >= tblAthScores.AthScore) AS AthRank
FROM tblAthScores
ORDER BY tblAthScores.AthScore DESC;

The query produces:
AthID AthScore AthRank
102 23.6 1
177 22.4 2
179 21.9 4
123 21.9 4
103 19.5 5
215 18.4 6

Here (finally!) is my question. I'd like to know how to
a) get AthRank to show 1, 2, 3, 3, 5, 6
b) get AthRank to show 1, 2, 3, 3, 4, 5

Thanks in advance for any help. I'm really stuck.

 

[Sarah]

cinnie

your question a) is an easy fix. try this.

SELECT tblAthScores.AthID, tblAthScores.AthScore,
1+(SELECT Count(*)
FROM tblAthScores AS Copy
WHERE Copy.AthScore > tblAthScores.AthScore) AS AthRank
FROM tblAthScores
ORDER BY tblAthScores.AthScore DESC;

I added 1 to the subquery count, and changed the ">=" to ">". this gives
values of AthRank as 1,2,3,3,5,6 instead of 1,2,4,4,5,6

I still have no idea how to get your question b) an AthRank of 1,2,3,3,4,5

Sarah

 

[John Spencer]

To solve B you would need something like the following

FirstQuery: Rank the unique scores
SELECT A.AthScore, 1+Count(B.AthScore) as RANK
FROM
(SELECT DISTINCT AthScore
FROM tblAthScores) As A
LEFT JOIN
(SELECT DISTINCT AthScore
FROM tblAthScores) As B
ON A.AthScore > B.AthScore
GROUP BY A.AthScore

Now you can use that query to assign ranking to the AthScores
SELECT tblAthScores.AthID, tblAthScores.AthScore,
Q.Rank
FROM tblAthScores INNER JOIN FirstQuery as Q
ON tblAthScores.AthScore = Q.AthScore
ORDER BY tblAthScores.AthScore DESC;

An all in one query would be something like the following
SELECT tblAthScores.AthID, tblAthScores.AthScore,
Q.Rank
FROM tblAthScores INNER JOIN
(SELECT A.AthScore, 1+Count(B.AthScore) as RANK
FROM
(SELECT DISTINCT AthScore
FROM tblAthScores) As A
LEFT JOIN
(SELECT DISTINCT AthScore
FROM tblAthScores) As B
ON A.AthScore > B.AthScore
GROUP BY A.AthScore) AS Q
ON tblAthScores.AthScore = Q.AthScore
ORDER BY tblAthScores.AthScore DESC;
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[cinnie]

John
Thank you for this very detailed reply. I'm looking forward to sorting
through and fully understanding it, then modifying it to suit a few other
programming needs I've encountered.

 

[cinnie]

Hello again John and others

I have a syntax question regarding your SQL code that puzzles me. I entered
the query exactly as you have shown it but, after saving, the FROM line
automatically changed from ...

FROM (SELECT DISTINCT AthScore FROM C_tblAthScores) AS A LEFT JOIN (SELECT
DISTINCT AthScore FROM C_tblAthScores) AS B ON A.AthScore<B.AthScore
to....

FROM [SELECT DISTINCT AthScore FROM C_tblAthScores]. AS A LEFT JOIN
[SELECT DISTINCT AthScore FROM C_tblAthScores]. AS B ON
A.AthScore<B.AthScore

Now the query works perfectly!! I'm puzzled by the "." that has appeared
after the "]" in two places. What does this mean?

thank you
cinnie

 

[John Spencer]

It means that Access has its own (non-standard) method of handling sub-queries
in the FROM clause. And that method means you CANNOT have square brackets in
the sub-query. If you do, then the query will generate a syntax error.

Fortunately, your table and field names followed the naming guidelines (ONLY
Letters, Numbers, and the underscore character AND no reserved words).

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Hello again John and others

I have a syntax question regarding your SQL code that puzzles me. I entered
the query exactly as you have shown it but, after saving, the FROM line
automatically changed from ...

FROM (SELECT DISTINCT AthScore FROM C_tblAthScores) AS A LEFT JOIN (SELECT
DISTINCT AthScore FROM C_tblAthScores) AS B ON A.AthScore<B.AthScore
to....

FROM [SELECT DISTINCT AthScore FROM C_tblAthScores]. AS A LEFT JOIN
[SELECT DISTINCT AthScore FROM C_tblAthScores]. AS B ON
A.AthScore<B.AthScore

Now the query works perfectly!! I'm puzzled by the "." that has appeared
after the "]" in two places. What does this mean?

thank you
cinnie

To solve B you would need something like the following

FirstQuery: Rank the unique scores
SELECT A.AthScore, 1+Count(B.AthScore) as RANK
FROM
(SELECT DISTINCT AthScore
FROM tblAthScores) As A
LEFT JOIN
(SELECT DISTINCT AthScore
FROM tblAthScores) As B
ON A.AthScore > B.AthScore
GROUP BY A.AthScore

Now you can use that query to assign ranking to the AthScores
SELECT tblAthScores.AthID, tblAthScores.AthScore,
Q.Rank
FROM tblAthScores INNER JOIN FirstQuery as Q
ON tblAthScores.AthScore = Q.AthScore
ORDER BY tblAthScores.AthScore DESC;

An all in one query would be something like the following
SELECT tblAthScores.AthID, tblAthScores.AthScore,
Q.Rank
FROM tblAthScores INNER JOIN
(SELECT A.AthScore, 1+Count(B.AthScore) as RANK
FROM
(SELECT DISTINCT AthScore
FROM tblAthScores) As A
LEFT JOIN
(SELECT DISTINCT AthScore
FROM tblAthScores) As B
ON A.AthScore > B.AthScore
GROUP BY A.AthScore) AS Q
ON tblAthScores.AthScore = Q.AthScore
ORDER BY tblAthScores.AthScore DESC;
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

.