Friday, January 04, 2013 1:33 AM
I have a query in Access that sorts through running event results, selects the top 3 points by school on all courses above course #1, and lists the 3 top WholeNames in that query as a "school team" that will later be given a total team score. The SQL below works in all cases except for a tie at third-best on the team, when the top 4 WholeNames are listed.
This means that a team with a tie at 3rd gets more points than one with a tie at 1st or 2nd; the tie must be broken, with only one name at the third-place position. What am I missing, and is there a simpler way to do this select function?
SELECT tbl_CleanedFinishData.Course, tbl_CleanedFinishData.MeetCode, tbl_CleanedFinishData.TeamCode, tbl_CleanedFinishData.Points, tbl_CleanedFinishData.WholeName, tbl_CleanedFinishData.SchoolCode, tbl_CleanedFinishData.NumPlace, tblSchoolCodes.School
FROM tbl_CleanedFinishData INNER JOIN tblSchoolCodes ON tbl_CleanedFinishData.SchoolCode = tblSchoolCodes.SchoolCode
WHERE (((tbl_CleanedFinishData.Course)>"1") AND ((tbl_CleanedFinishData.WholeName) In (SELECT TOP 3 WholeName
FROM tbl_CleanedFinishData AS Dupe
WHERE Dupe.MeetCode = tbl_CleanedFinishData.MeetCode AND Dupe.TeamCode = tbl_CleanedFinishData.TeamCode
ORDER BY Dupe.Points DESC)) AND ((tbl_CleanedFinishData.NumPlace)>0))
ORDER BY tbl_CleanedFinishData.MeetCode, tbl_CleanedFinishData.TeamCode, tbl_CleanedFinishData.Points DESC;
Friday, January 04, 2013 1:45 AMModerator
What happens if you add
SELECT FD.Course, FD.MeetCode, FD.TeamCode, FD.Points, FD.WholeName, FD.SchoolCode, FD.NumPlace, SC.School FROM tbl_CleanedFinishData AS FD INNER JOIN tblSchoolCodes AS SC ON FD.SchoolCode = SC.SchoolCode WHERE FD.Course>"1" AND FD.WholeName In (SELECT TOP 3 WholeName FROM tbl_CleanedFinishData AS Dupe WHERE Dupe.MeetCode = FD.MeetCode AND Dupe.TeamCode = FD.TeamCode ORDER BY Dupe.Points DESC, Dupe.WholeName) AND FD.NumPlace>0 ORDER BY FD.MeetCode, FD.TeamCode, FD.Points DESC;
In other words, I attempted to add WholeName as a second ORDER BY column to break the ties.
For every expert, there is an equal and opposite expert. - Becker's Law
- Marked As Answer by Bob Forgrave Friday, January 04, 2013 2:39 PM
Friday, January 04, 2013 2:40 PM
That worked perfect. Thanks, Naomi!