Look at the ranking number at the left. The code below return the following:1 8 Bird Twitty 50 52Kg Female Benchpress 176.63 137.52 24 Krige Mary 50.7 52Kg Female Benchpress 177.95 1403 25 Beukes Larney 51.3 52Kg Female Benchpress 182.66 1454 12 du Toit Carin 50 52Kg Female Benchpress 180.1 140.24 15 Boob Betty 50 52Kg Female Benchpress 183.06 142.56 13 Colbert Jackie 50 52Kg Female Benchpress 180.49 140.57 11 Wade Jenna 50 52Kg Female Benchpress 173.42 1351 7 DeDee Jenna 62 67.5Kg Female Benchpress 190.79 175.5
How do I change the code to return the rank correctly : 1 8 Bird Twitty 50 52Kg Female Benchpress 176.63 137.52 24 Krige Mary 50.7 52Kg Female Benchpress 177.95 1403 25 Beukes Larney 51.3 52Kg Female Benchpress 182.66 1454 12 du Toit Carin 50 52Kg Female Benchpress 180.1 140.25 15 Boob Betty 50 52Kg Female Benchpress 183.06 142.56 13 Colbert Jackie 50 52Kg Female Benchpress 180.49 140.57 11 Wade Jenna 50 52Kg Female Benchpress 173.42 1351 7 DeDee Jenna 62 67.5Kg Female Benchpress 190.79 175.5The code must take the ties into account.SELECT (SELECT COUNT(*) + 1 FROM History H INNER JOIN Lifter L ON L.uidLifter = H.uidLifterRef WHERE L.uidClassRef = Lifter.uidClassRef AND H.ScoreTotal > History.ScoreTotal) 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 ScoreTotalFROM Class, Lifter, Team, History, Meet, Activity, Events A, Events B, Events CWHERE (Meet.MeetDate = DATE ()) AND Team.TeamStatus = 0 AND 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.uidEventsGROUP BY uidClassRef, Lifter.LifterNumber, Lifter.Lastname, Lifter.Firstname, Lifter.BodyWeight, Class.Description, Lifter.Gender, Rank, History.ScoreTotal, Activity.Description, Activity.Activity, Lifter.LifterStatus, A.Name, B.Name, C.Name, History.ScoreA, History.ScoreB, History.ScoreC, History.SubTotalA, History.SubTotalB, History.SubTotalCHAVING (Activity.Activity = 'GenderStatus') AND (Lifter.LifterStatus = 0)ORDER BY VAL(Class.Description) ASC, Lifter.Gender DESC, History.ScoreTotal DESC, Rank DESC