Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2004
    Posts
    48

    Unanswered: Selecting first 25 records of group

    In an Access query, how do get only the first 25 records per group in a set of groups that have 25 or more records each?

    I will be happy to try and explain this with more detail if this isn't clear. Thanks for any help offered.

  2. #2
    Join Date
    Aug 2005
    Location
    D/FW, Texas, USA
    Posts
    78
    More detail would probably be good...

    If you are just looking for a single Group, then you can use SELECT TOP 25 * FROM ...

    If you have a finite set of groups the Easiest thing to do might be to just use a UNION query to join the SELECT Top 25 query for each of your categories.

    I'm not sure if there is a way to create a Dynamic Query that will return the top 25 for each group regardless of the number of groups. In SQL Server you could use a Cursor to dynamically create the UNION queries, but that doesn't help you much here...
    --wayne
    SELECT * FROM Users WHERE Clue>0
    0 rows returned

  3. #3
    Join Date
    Aug 2004
    Posts
    48

    Selecting first 25 of a group

    Thank you Wayne. Yes, the problem is that there are muliple groups and I need to select the first 25 from each group. If more detail is required, let me know.

  4. #4
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    I think Wayne has already provided the answer. For multiple queries your sql will look like:

    SELECT TOP 25 * FROM groupA
    UNION
    SELECT TOP 25 * FROM groupB
    UNION
    SELECT TOP 25 * FROM groupC
    etc..

    The fields you select from each table must be of the same data type.

    Chris

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You can do this with n groups (i.e. an unknown number) without dynamic SQL. The difficult bit is the "first 25 records per group". You need to define "first" in an order by clause. Sample SQL:

    Code:
    SELECT My_ID, My_Group_Field
    FROM MyTable
    WHERE  My_ID IN (SELECT TOP 25 My_ID
           FROM  MyTable B
           WHERE  B.My_Group_Field = dbo.MyTable.My_Group_Field
        ORDER BY My_ID)
    ORDER BY My_Group_Field, My_ID
    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Aug 2005
    Location
    D/FW, Texas, USA
    Posts
    78
    I knew there was a way to get there, but that's not one I tried last night ...

    I was also trying to avoid something where it ran the select for each row in the databse. If there is a large number of records, it's efficient for coding, but is very inefficient on the processor...
    --wayne
    SELECT * FROM Users WHERE Clue>0
    0 rows returned

Posting Permissions

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