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

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

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

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.

Go to Top of Page
   

- Advertisement -