Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Top 5 by Group in Query (A97)

    How can you query to get the top 5 results by group in a group query?

    I have a table which lists complaints by department. I need to be able to pull out the last 5 complaints for each department.

    TIA

    Peter

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Top 5 by Group in Query (A97)

    See ACC: How to Create a Top Values Per Group Report

    If I find how to do it in a query, I'll post it.

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Top 5 by Group in Query (A97)

    This requires a subquery. Let's suppose your tblComplaints has fields for Dept, ComplaintNo, and ComplaintDate. Your query would be something like this:

    Select Dept, ComplaintDate, ComplaintNo from tblComplaints where ComplaintNo In (Select top 5 ComplaintNo from tblComplaints as C WHERE C.Dept=tblComplaints.Dept ORDER BY ComplaintDate DESC)
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Top 5 by Group in Query (A97)

    Thanks Hans.

    I may end up exporting the data to excel when it is all sorted so a query would be best. I may just end up doing it in code but I think that it will be too slow.

    Peter

  5. #5
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Top 5 by Group in Query (A97)

    Thanks Mark
    This will give me something to go away and paly with for a couple of hours <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Peter

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Top 5 by Group in Query (A97)

    In ADO, you can use shaped recordsets to do this. See <post#=243358>post 243358</post#> but in A97 you're probably stuck with subqueries.
    Charlotte

  7. #7
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Top 5 by Group in Query (A97)

    Just adding my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> with one more reference... although thanks to Mark I didn't have to wade through them anymore <img src=/S/cool.gif border=0 alt=cool width=15 height=15> <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35> <img src=/S/cool.gif border=0 alt=cool width=15 height=15>...
    Some Database Journal query tutorials might come in handy for those who want to get some more theory (though I have too little experience to know whether this is the best on-line reference...). FYI, these are the links: Subqueries, part 1,Using the TOP keyword and Subqueries, part 2

Posting Permissions

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