Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Query Help 1

Status
Not open for further replies.

mkallover

Programmer
Feb 6, 2008
88
US
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:

Code:
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.
 
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
 
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!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top