Rank question

  • Thread starter Thread starter cinnie
  • Start date Start date
C

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.
 
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
 
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
 
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.
 
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
 
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


John Spencer said:
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

.
Click to expand...
Click to expand...
 
Back
Top