cancel
Showing results for 
Search instead for 
Did you mean: 

Select a max(date field) in an outer join

Former Member
7,010

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?

Accepted Solutions (0)

Answers (1)

Answers (1)

VolkerBarth
Contributor

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.