none
Top 3 run times per group RRS feed

  • Question

  • Dear MS Access Experts,

    I looked at a few different sites* and discussions talking about selecting the TOP 3,4,5,etc. records for a group.
    I tried to do exactly the same but somehow it my case it does not work. The statement returns all rows and not the top 3 per component_name.
    All I need is the top 3 run times for each component.

    SELECT Job_Log.Component_Name, Job_Log.[Component run time], Job_Log.[Average run time], Job_Log.[Component Start Time], Job_Log.[Component End Time], Job_Log.[Rows Inserted], Job_Log.ID
    FROM Job_Log
    WHERE ID IN (

    SELECT TOP 3 ID
        FROM Job_Log as Temp
        WHERE Temp.ID = Job_Log.ID
        ORDER By [Average run time] DESC
    )

    ORDER BY Job_Log.Component_Name, Job_Log.[Component run time] DESC;


    *http://www.allenbrowne.com/subquery-01.html#TopN
    Monday, April 10, 2017 12:10 PM

Answers

  • Try this:

    SELECT JL1.Component_Name, JL1.[Component run time], JL1.[Average run time],
    JL1.[Component Start Time], JL1.[Component End Time], JL1.[Rows Inserted], JL1.ID
    FROM Job_Log AS JL1 LEFT JOIN Job_Log AS JL2
    ON (JL2.Component_Name = JL1.Component_Name)
    AND (JL2.[Average run time]>JL1.[Average run time])
    GROUP BY JL1.Component_Name, JL1.[Component run time], JL1.[Average run time],
    JL1.[Component Start Time], JL1.[Component End Time], JL1.[Rows Inserted], JL1.ID
    HAVING COUNT(JL2.ID) < 3
    ORDER BY JL1.Component_Name, JL1.[Average run time] DESC;

    Ken Sheridan, Stafford, England

    • Marked as answer by metallon123 Tuesday, April 11, 2017 10:13 AM
    Monday, April 10, 2017 4:40 PM

All replies

  • You want the top 3 run times, not the top 3 IDs, so try

    SELECT Component_Name, [Component run time], [Average run time], [Component Start Time], [Component End Time], [Rows Inserted], ID
     FROM Job_Log
     WHERE [Average run time] IN (
     SELECT TOP 3 Temp.[Average run time]
         FROM Job_Log as Temp
         WHERE Temp.ID = Job_Log.ID
         ORDER By Temp.[Average run time] DESC
     )
    ORDER BY Component_Name, [Component run time] DESC;

    (Perhaps sort the main query by Average run time too?)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, April 10, 2017 2:22 PM
  • Try this:

    SELECT JL1.Component_Name, JL1.[Component run time], JL1.[Average run time],
    JL1.[Component Start Time], JL1.[Component End Time], JL1.[Rows Inserted], JL1.ID
    FROM Job_Log AS JL1 LEFT JOIN Job_Log AS JL2
    ON (JL2.Component_Name = JL1.Component_Name)
    AND (JL2.[Average run time]>JL1.[Average run time])
    GROUP BY JL1.Component_Name, JL1.[Component run time], JL1.[Average run time],
    JL1.[Component Start Time], JL1.[Component End Time], JL1.[Rows Inserted], JL1.ID
    HAVING COUNT(JL2.ID) < 3
    ORDER BY JL1.Component_Name, JL1.[Average run time] DESC;

    Ken Sheridan, Stafford, England

    • Marked as answer by metallon123 Tuesday, April 11, 2017 10:13 AM
    Monday, April 10, 2017 4:40 PM
  • Hello,

    Remove "WHERE Temp.ID = Job_Log.ID"  then I think you could get the top three record.

    But I suggest you share your data here and tell us your expected result.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, April 11, 2017 6:37 AM
    Moderator