Hi,
I wrote the query below and everything works fine except that the rank does not display correctly in the case of a tie. The calculated rank after the ties would not be correct due to this code in the query:
Code:
(SELECT COUNT(*) + 1
FROM History H INNER JOIN
Lifter L ON L.uidLifter = H.uidLifterRef
WHERE H.ScoreTotal > History.ScoreTotal AND
L.Gender = Lifter.Gender) AS Rank
I found a solution to the problem, in the link below, but I must admit that I have
no clue on how to implement that solution in my query to solve the problem. I know that I have to write two small queries that will be used in this larger one.
Here is the link to the solution :
http://support.microsoft.com/kb/207626/EN-US/
The database can be downloaded from
http://www.reitzgh.co.za/add/Database.zip
Make sure to update the MeetDate in the Meet table to reflect the current date before you try to run the query.
Code:
SELECT (SELECT COUNT(*) + 1
FROM History H INNER JOIN
Lifter L ON L.uidLifter = H.uidLifterRef
WHERE H.ScoreTotal > History.ScoreTotal AND
L.Gender = Lifter.Gender) AS Rank,
Lifter.LifterNumber AS LifterNumber,
Lifter.Lastname AS Lastname, Lifter.Firstname AS Firstname,
Lifter.BodyWeight, Class.Description AS CDescription,
Activity.Description AS GDescription, A.Name AS NameA,
History.ScoreA, History.SubTotalA, B.Name AS NameB,
History.ScoreB, History.SubTotalB, C.Name AS NameC,
History.ScoreC, History.SubTotalC,
History.ScoreTotal AS ScoreTotal
FROM Class, Lifter, Team, History, Meet, Activity, Events A,
Events B, Events C
WHERE History.ScoreTotal > 0 AND
Class.uidClass = Lifter.uidClassRef AND
Lifter.uidTeamRef = Team.uidTeam AND
Lifter.uidLifter = History.uidLifterRef AND
Team.uidMeetRef = Meet.uidMeet AND
Lifter.Gender = Activity.Code AND
Meet.uidEventsA = A.uidEvents AND
Meet.uidEventsB = B.uidEvents AND
Meet.uidEventsC = C.uidEvents AND
Team.TeamStatus = 0 AND Lifter.LifterStatus = 0 AND
(MeetDate = DATE ())
GROUP BY History.ScoreTotal, uidClassRef, Lifter.LifterNumber,
Lifter.Lastname, Lifter.Firstname, Lifter.BodyWeight,
Class.Description, History.WeightScoreC, Lifter.Gender,
Activity.Description, Activity.Activity, Lifter.LifterStatus,
A.Name, B.Name, C.Name, History.ScoreA, History.ScoreB,
History.ScoreC, History.SubTotalA, History.SubTotalB,
History.SubTotalC
HAVING (Activity.Activity = 'GenderStatus') AND
(Lifter.LifterStatus = 0)
ORDER BY Lifter.Gender ASC, History.ScoreTotal DESC,
VAL(Class.Description) DESC, Lifter.BodyWeight ASC
Found the solution but do'nt know how to implement
