Return top 2 records per group in a query

Ethvia

Board Regular
Joined
Mar 10, 2009
Messages
63
Good morning. I've done alot of 'googling' and found examples of how to do this, but cannot seem to make it work, so thought I'd ask here.

I have a table that contains the response times to a request and each request can have a dozen or more responses. I'm trying to get the query to return only the first 2 response times to each request.

My table (tblTest03FlowAfterOrder) is setup as follows: (each account can have multiple 'requestplaced' times, I just copied a few in here so you can see what it looks like.

ACCOUNTRequestPlacedRespondTimeUniqueRequestID
37798858474/13/2019 2:25:00 PM4/13/2019 3:21:00 PM370364941
37798858474/13/2019 2:25:00 PM4/13/2019 8:27:00 PM370364941
37798858474/13/2019 2:25:00 PM4/14/2019 3:20:00 AM370364941
37798858474/13/2019 2:25:00 PM4/14/2019 8:00:00 AM370364941
37798858474/15/2019 2:25:00 PM4/16/2019 1:32:00 AM370364947
37798858474/15/2019 2:25:00 PM4/17/2019 2:43:00 AM370364947
37798858474/15/2019 2:25:00 PM4/20/2019 2:43:00 AM370364947
38172389009/2/2019 3:42:00 PM9/3/2019 1:17:00 AM383139864
38172389009/2/2019 3:42:00 PM9/3/2019 4:00:00 AM383139864
38172389009/2/2019 3:42:00 PM9/3/2019 5:15:00 AM383139864


And here's my query setup:

SELECT PR1.Account, PR1.RespondTime, PR1.UniqueRequestID
FROM tblTest03FlowAfterOrder as PR1
WHERE PR1.[RespondTime] IN (
SELECT TOP 2 PR1.RespondTime
FROM tblTest03FlowAfterOrder as PR2
WHERE PR2.UniqueRequestID = PR1.UniqueRequestID
ORDER BY PR2.RespondTime ASC
)
ORDER BY UniqueRequestID, RespondTime


The trouble is my query returns all of the results, not just the top 2. I'm sure I'm doing something wrong...I just can't figure out what. Does anyone see a blatant issue with my query?

thanks!
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Try this
VBA Code:
SELECT account
    ,uniquerequestId
    ,respondtime
FROM accountuk AS PR1
WHERE pr1.RespondTime IN (
        SELECT TOP 2 PR2.RespondTime
        FROM accountuk AS PR2
        WHERE pr1.UniqueRequestID = pr2.UniqueRequestID
        ORDER BY PR2.RespondTime ASC
        )
GROUP BY account
    ,pr1.UniqueRequestID
    ,respondtime;

Change table name to your own.

Result:


accountuniquerequestIdrespondtime
37798858473703649414/13/2019 3:21:00 PM
37798858473703649414/13/2019 8:27:00 PM
37798858473703649474/16/2019 1:32:00 AM
37798858473703649474/17/2019 2:43:00 AM
38172389003831398649/3/2019 1:17:00 AM
38172389003831398649/3/2019 4:00:00 AM
 
Upvote 0
THANK YOU!!!!!!!!!!!!!!!!!!!!

That works perfectly. I'll dig through it and try to find my error, but regardless, I have something that works now and really appreciate it!
 
Upvote 0
You are welcome. Happy to help.
Good luck with your project.
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top