What's wrong with this code - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

What's wrong with this code

Author

Topic

TenTwenty
Starting Member

22 Posts

Posted - 2004-12-30 : 08:05:00

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 Score
1 8 Bird Twitty 52Kg Female 684.69
2 12 du Toit Carin 52Kg Female 608.77
3 13 Colbert Jackie 52Kg Female 607.62
4 11 Wade Jenna 52Kg Female 595.41
1 7 DeDee Jenna 67.5Kg Female 702.27
2 10 Diva Meisie 67.5Kg Female 550.46
1 5 Man Super 70Kg Male 655.09
1 9 Doe John 82.5Kg Male 480.3
1 3 Man Spider 90Kg Male 537.63
1 6 America Mr 100Kg Male 406.42
1 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 ScoreTotal
FROM Class, Lifter, Team, History, Meet, Activity
WHERE (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.ScoreTotal
HAVING (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.

TenTwenty
Starting Member

22 Posts

Posted - 2005-01-07 : 03:58:07

Here is the solution for the code. It only works for top 3 and take ties into acccount.

SELECT (IIF
        ((SELECT COUNT(H.scoretotal) + 1
       FROM History AS H INNER JOIN
           Lifter L ON L.uidLifter = H.uidLifterRef
       WHERE L.uidClassRef = L1.uidClassRef AND 
           H.ScoreTotal > H1.ScoreTotal) >= 4,
        (SELECT COUNT(H.ScoreTotal)
      FROM History AS H INNER JOIN
           Lifter L ON L.uidLifter = H.uidLifterRef
      WHERE L.uidClassRef = L1.uidClassRef AND 
           H.ScoreTotal > H1.ScoreTotal),
        (SELECT COUNT(H.ScoreTotal) + 1
      FROM History AS H INNER JOIN
           Lifter L ON L.uidLifter = H.uidLifterRef
      WHERE L.uidClassRef = L1.uidClassRef AND 
           H.ScoreTotal > H1.ScoreTotal))) AS Rank, L1.uidLifter
FROM Class, Lifter AS L1, Team, History AS H1, Meet, Activity AS Gen
WHERE (Meet.MeetDate = DATE ()) AND Team.TeamStatus = 0 AND 
    H1.ScoreTotal > 0 AND Class.uidClass = L1.uidClassRef AND 
    L1.uidTeamRef = Team.uidTeam AND 
    L1.uidLifter = H1.uidLifterRef AND 
    Team.uidMeetRef = Meet.uidMeet AND 
    L1.Gender = Gen.Code AND StatusInTeam <= 1 AND 
    (H1.ScoreTotal) IN
        (SELECT Top 3 ScoreTotal
      FROM History H7 INNER JOIN
           Lifter L7 ON L7.uidLifter = H7.uidLifterRef
      WHERE L7.uidClassRef = L1.uidClassRef AND 
           L7.Gender = L1.Gender
      GROUP BY uidClassRef, ScoreTotal
      ORDER BY L1.Gender DESC, H7.ScoreTotal DESC)
GROUP BY uidClassRef, L1.uidLifter, H1.ScoreTotal, L1.BodyWeight, Class.Description, L1.Gender, Gen.Description, Gen.Activity, L1.LifterStatus
HAVING (Gen.Activity = 'GenderStatus') AND (L1.LifterStatus = 0)
ORDER BY VAL(Class.Description), L1.Gender DESC , H1.ScoreTotal DESC , L1.BodyWeight;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources