The pseudo-code of what I want to write is: select c.*, max(d.last_date) into #client_last_contact from #client_list c left outer join #all_previous_contacts d on c.uid = d.uid and c.area_code = d.area_code
The primary key of #client_list is uid and area_code. The table was created for a specific date range. Now I want to find the most recent contact prior to the date range, but there will be UIDs who have not been contacted before. Hence the outer join. Running the query above, iSQL asks me to group all the columns in #client_list. Is there a way to restructure the query so a GROUP section is not needed?
asked 23 May '13, 02:41
Do you want to look for the maximum last_date by uid or by uid and area_code? Here's a solution that groups by both fields:
You can "prepare" the grouping in a derived query that just uses the "client table" and then join that with the "master table", something like:
select c.*, dt.max_last_date into #client_last_contact from #client_list c left outer join (select uid, area_code, max(d.last_date) as max_last_date from #all_previous_contacts d group by uid, area_code) dt on c.uid = dt.uid and c.area_code = dt.area_code
Apparently, that still does need a GROUP BY, but just for the PK values of the client table.