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;
Go to Top of Page
   

- Advertisement -