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

BalmainBoy's gravatar image

BalmainBoy
16112
accept rate: 0%


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.

permanent link

answered 23 May '13, 03:34

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

edited 23 May '13, 03:37

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×11
×7
×7

question asked: 23 May '13, 02:41

question was seen: 6,391 times

last updated: 23 May '13, 03:37