Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2012

    Unanswered: Top 5 records in each group (like Rank function in SQL)

    Hi all,

    I need to get top 5 records in each group.
    Query will make my explanation easier.

    select Tdiag1, P_t_Extc, COUNT(P_t_Extc) as [P_t_Extc Count]
    from DS1
    group by Tdiag1, P_t_Extc
    order by Tdiag1 desc, COUNT(P_t_Extc) desc

    Result set

    Tdiag1 P_t_Extc P_t_Extc Count
    ---------- ---------- --------------
    T391 X60 217
    T391 Y10 34
    T391 X40 33
    T391 X64 1
    T391 X65 1
    S526 W09 232
    S526 W02 95
    S526 W19 78
    S526 W01 66
    S526 W17 43
    S526 X59 37
    S526 W14 34
    S526 W03 29
    S526 W18 21
    S526 W13 21
    S526 V199 20
    S526 V180 17

    For Tdiag1 (S526) i want to show top 5 of P_t_Extc Count.
    I am doing in access 2007.
    Attached result set as gif image.

    Your help will be greatly appreciated.
    Thanks in advance.

    Attached Thumbnails Attached Thumbnails result_set.gif  

  2. #2
    Join Date
    Jun 2005
    Richmond, Virginia USA
    Provided Answers: 19
    Not my area of expertise, but I think the first question that needs to be asked, here, is where are you trying to do this, in a Form or in a Report?

    Linq ;0)>
    Hope this helps!

    The problem with making anything that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Mar 2009
    Provided Answers: 15
    You could try:
    SELECT Result_Set1.Tdiag1, Result_Set1.P_t_Extc, Result_Set1.[P_t_Extc Count]
    FROM Result_Set Result_Set1 INNER JOIN 
         Result_Set Result_Set2 ON Result_Set1.Tdiag1 = Result_Set2.Tdiag1 AND 
                                   Result_Set1.[P_t_Extc Count] <= Result_Set2.[P_t_Extc Count]
    GROUP BY Result_Set1.Tdiag1, Result_Set1.[P_t_Extc Count], Result_Set1.P_t_Extc
    HAVING COUNT(*) <=5
    ORDER BY Result_Set1.Tdiag1, Result_Set1.[P_t_Extc Count] DESC;
    Note: I named the source table "Result_Set".
    Attached Thumbnails Attached Thumbnails Qry_RankTop5.jpg  
    Have a nice day!

Posting Permissions

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