Query Help 1

[mkallover]

I'm trying to create a query that will select the first response in a table for any given request. The query I'm working with is:

SELECT request_id, response_from, MIN(response_date) AS MinResponse
FROM dbo_CPO_REQUEST_RESPONSE
WHERE response_from IN (SELECT username FROM dbo_ArgusUser WHERE dept_num IN ("20470","24000","45540","20465"))
GROUP BY request_id, response_from;

This almost gets me there but the problem is if there are responses from more than one user in dbo_ArgusUser it selects both. What I want is only the first request.

 

[PHV]

SELECT A.request_id,A.response_from,A.response_date
FROM dbo_CPO_REQUEST_RESPONSE A INNER JOIN (
SELECT R.request_id,MIN(R.response_date) AS MinResponse
FROM dbo_CPO_REQUEST_RESPONSE R INNER JOIN dbo_ArgusUser U ON R.response_from=A.username
WHERE A.dept_num IN ('20470','24000','45540','20465')
GROUP BY R.request_id
) B ON A.request_id=B.request_id AND A.response_date=B.MinResponse

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mkallover]

Wow, thanks. I'll have to take a look at that one to completely understand it but it works (I had to change lines 4 and 5 to reference U instead of A.)

Thanks!