Return top 2 records per group in a query

[Ethvia]

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.

ACCOUNT	RequestPlaced	RespondTime	UniqueRequestID
3779885847	4/13/2019 2:25:00 PM	4/13/2019 3:21:00 PM	370364941
3779885847	4/13/2019 2:25:00 PM	4/13/2019 8:27:00 PM	370364941
3779885847	4/13/2019 2:25:00 PM	4/14/2019 3:20:00 AM	370364941
3779885847	4/13/2019 2:25:00 PM	4/14/2019 8:00:00 AM	370364941
3779885847	4/15/2019 2:25:00 PM	4/16/2019 1:32:00 AM	370364947
3779885847	4/15/2019 2:25:00 PM	4/17/2019 2:43:00 AM	370364947
3779885847	4/15/2019 2:25:00 PM	4/20/2019 2:43:00 AM	370364947
3817238900	9/2/2019 3:42:00 PM	9/3/2019 1:17:00 AM	383139864
3817238900	9/2/2019 3:42:00 PM	9/3/2019 4:00:00 AM	383139864
3817238900	9/2/2019 3:42:00 PM	9/3/2019 5:15:00 AM	383139864

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!

 

[jackd]

Try this

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:

account	uniquerequestId	respondtime
3779885847	370364941	4/13/2019 3:21:00 PM
3779885847	370364941	4/13/2019 8:27:00 PM
3779885847	370364947	4/16/2019 1:32:00 AM
3779885847	370364947	4/17/2019 2:43:00 AM
3817238900	383139864	9/3/2019 1:17:00 AM
3817238900	383139864	9/3/2019 4:00:00 AM

 

[Ethvia]

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]

You are welcome. Happy to help.
Good luck with your project.