Results 1 to 5 of 5
  1. #1
    zx3 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Aug 2011
    Posts
    47

    Question Get one row from each group where mpt is highest in the group

    Hi all, I have this table, i need to use query to get one row from each group A, B and C, could be more if table is bigger, and this row from the specific group has to have highest mpt among the rows found within the group, and i can guarantee there is only one in each group in the big table below,



    cat time level pt mpt
    A 1 A1 7 0
    A 2 A3 3 3
    B 3 A2 4 0
    C 1 A3 1 1
    C 2 A1 9 0

    And the resulting table should look like this...

    cat time level pt mpt
    A 2 A3 3 3
    B 3 A2 4 0
    C 1 A3 1 1

    Please help, thanks.

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    something like

    Code:
    SELECT C.*
    FROM MyTable C INNER JOIN (SELECT Cat,max(mpt) FROM myTable GROUP BY Cat) M ON C.Cat=M.Cat

  3. #3
    zx3 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Aug 2011
    Posts
    47
    Dear Ajax,

    Thanks for the quick answer, unfortunately, the query is not working as expected, here is the fileDatabase5.accdb

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    sorry - missed off a join

    SELECT C.*
    FROM MyTable C INNER JOIN (SELECT Cat,max(mpt) maxmpt FROM myTable GROUP BY Cat) M ON C.Cat=M.Cat AND C.mpt=M.maxmpt

  5. #5
    zx3 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Aug 2011
    Posts
    47
    Dear Ajax,

    Thanks again, it works!!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 06-09-2015, 09:51 AM
  2. Replies: 3
    Last Post: 04-28-2015, 02:16 AM
  3. Replies: 0
    Last Post: 02-25-2013, 04:43 PM
  4. Replies: 2
    Last Post: 06-21-2012, 07:40 PM
  5. Replies: 1
    Last Post: 02-13-2012, 04:58 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums