Obviously there is something wrong with this statement but I can not figure out what?? Can someone please help me.The query below return this data in it's rows:Rank # Name Div Sex Score1 8 Bird Twitty 52Kg Female 684.692 12 du Toit Carin 52Kg Female 608.773 13 Colbert Jackie 52Kg Female 607.624 11 Wade Jenna 52Kg Female 595.411 7 DeDee Jenna 67.5Kg Female 702.272 10 Diva Meisie 67.5Kg Female 550.461 5 Man Super 70Kg Male 655.091 9 Doe John 82.5Kg Male 480.31 3 Man Spider 90Kg Male 537.631 6 America Mr 100Kg Male 406.421 4 Hulk Incred 110Kg Male 645.8
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, Class.Description AS CDescription, Activity.Description AS GDescription, History.ScoreTotal AS ScoreTotalFROM Class, Lifter, Team, History, Meet, ActivityWHERE (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 (Lifter.uidLifter IN (SELECT Top 3 Lt.uidLifter FROM Lifter Lt WHERE Lt.uidLifter = Lifter.uidLifter))GROUP BY uidClassRef, Lifter.LifterNumber, Lifter.Lastname, Lifter.Firstname, Class.Description, History.WeightScoreC, Lifter.Gender, Activity.Description, Activity.Activity, Lifter.LifterStatus, History.ScoreTotalHAVING (Activity.Activity = 'GenderStatus') AND (Lifter.LifterStatus = 0)ORDER BY VAL(Class.Description) ASC, Lifter.Gender DESC, History.ScoreTotal DESC
In the sample above I would like not see the record with lifter number 11 I would like to use this to award GOLD, SILVER and BRONZE to lifters.