Results 1 to 3 of 3
  1. #1
    Evald is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    2

    Sorting inside the GROUP BY groups

    Suppose there are multiple columns in the table. Four of them are called ID, A, B, C, and the rest... The ID column is a primary key and its values are unique. I want to create an SQL query which:
    1. grouped all rows by B, C
    2. sorted each group by A, ID


    3. returned ID of the first row of each sorted group
    I'm going to use this query as the subquery in WHERE part to filter rows of the same table (i.e. SELECT * FROM tble WHERE ID IN this_subquery).
    Is that possible?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Provide sample data and desired output.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Evald is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    2

    Done but looks cumbersome

    I managed to create the SQL which returns a result I need. But it looks to me very inelegant. Firstly I grouped rows by B, C and selected the rows with Min(A) from each group. Then I grouped those rows by B, C, min(A) and selected the rows with Min(ID).
    I've attached the mdb file. Due to the restrictions I had to zip it.
    I had to create three intermediate queries (qr_1, qr_2 ant qr_3), each consecutive query uses the result of the former one. The final query (qrResult) uses the result of the query qr_3 and returns the expected result.
    Then I tried to put texts of all four queries (qr_1, qr_2, qr_3 and qrResult) in to one query (qrAllInOne) and it worked as well.
    Attached Files Attached Files

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

Similar Threads

  1. Sorting on group sum
    By gradygoose in forum Reports
    Replies: 1
    Last Post: 02-28-2016, 06:57 PM
  2. Sorting on group totals
    By KMac in forum Reports
    Replies: 3
    Last Post: 10-16-2014, 02:33 PM
  3. Report with Age groups data under each group
    By jyellis in forum Reports
    Replies: 1
    Last Post: 03-10-2013, 05:12 PM
  4. Sorting within a group
    By BLD21 in forum Reports
    Replies: 3
    Last Post: 04-08-2011, 11:59 AM
  5. Group & Reports (more than 4 Groups??)
    By JohnS in forum Reports
    Replies: 3
    Last Post: 10-01-2009, 07:11 AM

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