Subqueries and ranking

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

 

[RoyVidar]

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.

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

WHERE tblEventResults.STimeResult > T.STimeResult) +1 AS FinishPlace

 

[John Spencer]

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

 

[Guest]

John,

You read my mind - how did you know what I wanted even before I did?

Thanks again!