Return top 2 records per group in a query

Ethvia

Board Regular
Joined
Mar 10, 2009
Messages
59
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!
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

jackd

Well-known Member
Joined
Oct 19, 2006
Messages
1,250
Office Version
365
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
 

Ethvia

Board Regular
Joined
Mar 10, 2009
Messages
59
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!
 

jackd

Well-known Member
Joined
Oct 19, 2006
Messages
1,250
Office Version
365
You are welcome. Happy to help.
Good luck with your project.
 

Forum statistics

Threads
1,089,635
Messages
5,409,453
Members
403,264
Latest member
naturally_data

This Week's Hot Topics

Top