Subqueries and ranking

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm ranking records in a query, but I'm having a little trouble getting the
ties to display the way I want.
Here's the sql:

SELECT tblEventResults.fkAthlete, tblEventResults.fkMeetEventID,
tblEventResults.STimeResult AS Expr1, (SELECT Count(*) FROM tblEventResults
AS T WHERE tblEventResults.STimeResult+1>T.STimeResult) AS FinishPlace
FROM tblEventResults
WHERE
(((tblEventResults.fkMeetEventID)=[Forms]![sbfSGrpTimeEntry]![fkMeetEventID]))
ORDER BY tblEventResults.STimeResult ASC, tblEventResults.fkAthlete;

My results are numbered 1, 3, 3, 4, 5, etc., but I want them to be 1, 2, 2,
4, 5, etc.

Can someone help?

Thanks.
 
Gina K said:
I'm ranking records in a query, but I'm having a little trouble
getting the ties to display the way I want.
Here's the sql:

SELECT tblEventResults.fkAthlete, tblEventResults.fkMeetEventID,
tblEventResults.STimeResult AS Expr1, (SELECT Count(*) FROM
tblEventResults AS T WHERE
tblEventResults.STimeResult+1>T.STimeResult) AS FinishPlace FROM
tblEventResults WHERE
(((tblEventResults.fkMeetEventID)=[Forms]![sbfSGrpTimeEntry]![fkMeetEventID]))
ORDER BY tblEventResults.STimeResult ASC, tblEventResults.fkAthlete;

My results are numbered 1, 3, 3, 4, 5, etc., but I want them to be 1,
2, 2, 4, 5, etc.

Can someone help?

Thanks.
Click to expand...

Here's one thing to try, move the "+1" part outside the subquery

WHERE tblEventResults.STimeResult > T.STimeResult) +1 AS FinishPlace
 
Try the following. I've included the fkMeetEventID on the guess that you
want to rank the finishers in the event. Note that this calculates first
place as 0, second as 1, etc. So we just add 1 to the result to get 1, 2,
2, 4, 5

SELECT tblEventResults.fkAthlete
, tblEventResults.fkMeetEventID
, tblEventResults.STimeResult AS Expr1
, (SELECT Count(*)
FROM tblEventResults AS T
WHERE T.STimeResult > tblEventResults.STimeResult AND
T.fkMeetEventID = tblEventResults.fkMeetEventID) + 1 AS
FinishPlace
FROM tblEventResults
WHERE
(((tblEventResults.fkMeetEventID)=[Forms]![sbfSGrpTimeEntry]![fkMeetEventID]))
ORDER BY tblEventResults.STimeResult ASC, tblEventResults.fkAthlete;

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top