Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    9

    Unhappy Unanswered: Display only the top 3 of a group

    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
    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

    I would like to see the query only returns the top three of each group to help me determine the GOLD,SILVER and BRONCE position in each division.

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    how do you plan to handle ties ??

    access native-handles "sports-style" ranking:
    joe, 600.35 -> 1st
    fred, 600.32 -> 2nd
    sam, 600.31 -> joint 3rd
    tim, 600.31 -> joint 3rd
    jeff, 600.31 -> joint 3rd
    tony, 600.00 -> not placed
    ...using SELECT TOP 3 * FROM

    what do you plan if the data is:
    joe, 600.35
    fred, 600.35
    sam, 600.32
    tim, 600.31
    jeff, 600.31
    tony, 600.00
    you can find (not really access-related, but useable) discussion courtesy of rudy here

    more difficult is another question posted here yesterday or today that asks for only 3 ... a sort-of SELECT TOP 3 WITHOUT TIES where any joint places are dropped (at random) once three ORDER DESC rows are selected
    ...that's still unanswered i guess.

    izy

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Mar 2004
    Posts
    9

    Question Darn, things are now getting ugly.....

    Hi,

    Thanks alot for pointing this to me! BUT, The "Top 3" in SQL will give you the top three, including matching based on the order by.

    My problem still is with the insertion of the select within the main select. The moment I insert the select statement for the top 3 I screw the whole statement. I know it can be done but the bunch of joins seems to confuse me. Any help perhaps?

  4. #4
    Join Date
    Mar 2004
    Posts
    9

    Cool Select not returning values as expected?

    I finally managed in changing the statement to run without errors but obviosly I got something wrong as I does not return the values as expected correctly. It just ignore the Select Top 3 and return everything like without the statement.

    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 (ScoreTotal IN
    (SELECT Top 3 ScoreTotal
    FROM History
    WHERE History.uidLifterRef = Lifter.uidLifter
    ORDER BY History.ScoreTotal DESC))

    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

  5. #5
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Hi Andreb,

    And might I say "Welcome to the Forum"
    Not sure if this will help but just thought I might try. In my Query for the Top 5 it then shows in the SQL view like this:

    Code:
    SELECT TOP 5 tblDateGrabber.PurchaseDate, tblDateGrabber.ToolNumber, tblDateGrabber.ToolName, tblDateGrabber.ToolID, tblDateGrabber.CheckInDate, tblDateGrabber.TodayDate, tblDateGrabber.txtMessageBox
    FROM tblDateGrabber;
    Maybe that will even kinda point you into a direction to nearly resolve your situation. At least I hope you can find the answer soon.

    Enjoy the Forum and
    have a nice one,
    BUD

  6. #6
    Join Date
    Mar 2004
    Posts
    9

    Talking Solution, ONLY for top 3

    Here is the solution to the original question and on how to get rid of the wrong ranking incase of any ties. This will ONLY work for TOP 3. Thanks to all for your contributions.

    Code:
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •