Query with Ties and a Rank - 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

Query with Ties and a Rank

Author

Topic

TenTwenty
Starting Member

22 Posts

Posted - 2005-01-06 : 06:07:20

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.5
2 24 Krige Mary 50.7 52Kg Female Benchpress 177.95 140
3 25 Beukes Larney 51.3 52Kg Female Benchpress 182.66 145
4 12 du Toit Carin 50 52Kg Female Benchpress 180.1 140.2
4 15 Boob Betty 50 52Kg Female Benchpress 183.06 142.5
6 13 Colbert Jackie 50 52Kg Female Benchpress 180.49 140.5
7 11 Wade Jenna 50 52Kg Female Benchpress 173.42 135
1 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.5
2 24 Krige Mary 50.7 52Kg Female Benchpress 177.95 140
3 25 Beukes Larney 51.3 52Kg Female Benchpress 182.66 145
4 12 du Toit Carin 50 52Kg Female Benchpress 180.1 140.2
5 15 Boob Betty 50 52Kg Female Benchpress 183.06 142.5
6 13 Colbert Jackie 50 52Kg Female Benchpress 180.49 140.5
7 11 Wade Jenna 50 52Kg Female Benchpress 173.42 135
1 7 DeDee Jenna 62 67.5Kg Female Benchpress 190.79 175.5

The 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 ScoreTotal
FROM Class, Lifter, Team, History, Meet, Activity, Events A, 
    Events B, Events C
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 
    Meet.uidEventsA = A.uidEvents AND 
    Meet.uidEventsB = B.uidEvents AND 
    Meet.uidEventsC = C.uidEvents
GROUP 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.SubTotalC
HAVING (Activity.Activity = 'GenderStatus') AND 
    (Lifter.LifterStatus = 0)
ORDER BY VAL(Class.Description) ASC, Lifter.Gender DESC, 
    History.ScoreTotal DESC, Rank DESC

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-01-06 : 10:01:56

You need to decide how you want ties to be handled before we can alter your SQL statement.

- Jeff

TenTwenty
Starting Member

22 Posts

Posted - 2005-01-07 : 01:19:37

Hi Jeff,

A tie would be called when lifters has the same weight division, bodyweight, and score.

BTW, I find this link that sure by the look of it looks like it could solve my problem. I did install it but will surely need some help in the implimantation of the concept. I'm not that much of an Access expert and if possible can you perhaps have a look. There is also a sample download on the page.

http://support.microsoft.com/kb/207626/EN-US/

TenTwenty
Starting Member

22 Posts

Posted - 2005-01-18 : 00:49:55

Check http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=44416 for a solution to the above question.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-18 : 02:52:44

Oh, Andre` :-)

This Jeff is too bright sql star to be pointed to someone's else sql stuff.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources