Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Select Top 3 without ties, still displyaing ties?

Answered Select Top 3 without ties, still displyaing ties?

  • Friday, January 04, 2013 1:33 AM
     
     
    Sign In to Vote
    0

    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;

    Thanks,

    Bob

     

All Replies

  • Friday, January 04, 2013 1:45 AM
    Moderator
     
     Answered Has Code
    Sign In to Vote
    1

    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


    My blog

    • Marked As Answer by Bob Forgrave Friday, January 04, 2013 2:39 PM
    •  
     
  • Friday, January 04, 2013 2:40 PM
     
     
    Sign In to Vote
    0

    That worked perfect. Thanks, Naomi!

    Bob