June 12, 2006 at 10:16 am
I have a table with ID, companyID, contacted_date, follow_up_date. I would like a query to return the most recent contacted_date for each distinct companyID, as well as the related follow_up_date for that most recent contacted date row. It is the last detail that I am having difficulty with for some reason. Any help would be appreciated,
Andrew
June 12, 2006 at 10:33 am
Can you post your query and DDL?
June 12, 2006 at 10:35 am
Use a derived table, such as
SELECT CompanyID,
Contacted_Date,
FollowUp_Date
FROM MyTable
INNER JOIN (
SELECT CompanyID,
MAX(Contacted_Date) theDate
FROM MyTable
GROUP BY CompanyID
) z ON z.CompanyID = MyTable.CompanyID AND z.theDate = MyTable.Contacted_Date
N 56°04'39.16"
E 12°55'05.25"
June 12, 2006 at 10:52 am
Something like this?
SELECT DISTINCT a.CompanyID,
b.Contacted_Date,
b.FollowUp_Date
FROM #Temp a
INNER JOIN (
SELECT c.CompanyID, MAX(c.Contacted_Date) AS Contacted_Date, MAX(c.FollowUp_Date) AS FollowUp_Date
FROM #Temp c
WHERE c.CompanyID = CompanyID
GROUP BY c.CompanyID
) b
ON a.CompanyID = b.CompanyID
June 12, 2006 at 12:09 pm
Peter, you're version gives me what I was looking for. Thanks for your help guys,
Andrew
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply