Top 5 by Group in Query (A97)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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)

Reply | Quote

Thanks Mark
This will give me something to go away and paly with for a couple of hours

Peter

Reply | Quote

Just adding my with one more reference… although thanks to Mark I didn’t have to wade through them anymore …
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

Reply | Quote

Just adding my with one more reference… although thanks to Mark I didn’t have to wade through them anymore …
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

Reply | Quote

In ADO, you can use shaped recordsets to do this. See post 243358 but in A97 you’re probably stuck with subqueries.

Reply | Quote