Select max date by grouping?

Question

PLEASE will someone help? I've put HOURS into this silly, stupid problem. This stackoverview post is EXACTLY my question, and I have tried BOTH suggested solutions to no avail.

Here are MY specifics. I have extracted 4 records from my actual database, and excluded no fields:

master_id   date_sent   type    mailing response
00001   2015-02-28 00:00:00 PHONE   NULL    NULL
00001   2015-03-13 14:45:20 EMAIL   ThankYou.html   NULL
00001   2015-03-13 14:34:43 EMAIL   ThankYou.html   NULL
00001   2015-01-11 00:00:00 EMAIL   KS_PREVIEW  TRUE
00001   2015-03-23 21:42:03 EMAIL   MailChimp Update #2 NULL

(sorry about the alignment of the columns.)

I want to get the most recent mailing and date_sent for each master_id. (My extract is of only one master_id to make this post simple.)

So I run this query:

SELECT master_id,date_sent,mailing
FROM contact_copy
WHERE type="EMAIL"

and get the expected result:

master_id   date_sent   mailing         
1   3/13/2015   14:45:20    ThankYou.html       
1   3/13/2015   14:34:43    ThankYou.html       
1   1/11/2015   0:00:00 KS_PREVIEW      
1   3/23/2015   21:42:03    MailChimp   Update  #2

BUT, when I add this simple aggregation to get the most recent date:

SELECT master_id,max(date_sent),mailing
FROM contact_copy
WHERE type="EMAIL"
group BY master_id
;

I get an UNEXPECTED result:

master_id   max(date_sent)  mailing
00001   2015-03-23 21:42:03 ThankYou.html

So my question: why is it returning the WRONG MAILING?

It's making me nuts! Thanks.

By the way, I'm not a developer, so sorry if I'm breaking some etiquette rule of asking. :)

Answer 1

That's because when you use GROUP BY, all the columns have to be aggregate columns, and mailing is not one of them..

You should use a subquery or a join to make it work

SELECT master_id,date_sent,mailing
FROM contact_copy cc
JOIN 
( SELECT master_id,max(date_sent)
  FROM contact_copy
  WHERE type="EMAIL"
  group BY master_id
 ) result
ON cc.master_id= result.master_id AND cc.date_sent=result.date_sent