mysql - SQL get only data associated with a minimum date elsewhere in database

Question

I want to use Netflix as an example for this question since everyone knows who they are. With Netflix, there is an account holder and there are users.

Using my own Netflix account as an example, I am the account holder (I pay for it each month), my partner is a user as is my room mate. So 1 account and 3 users.

We want to know what marketing channel to credit the account to. Marketing channel is a user level data point and not account level. So we are basing it on the logic of how the first account user found us and then want to apply that to the account.

Here is what the query I'm working on looks like:

SELECT 
    my.data AS Account_ID,
    co.ID AS User_ID,
    def.def_medium,
    MIN(co.created) # Don't ask why I used MIN here, was hoping it'd act like a WHERE clause.
FROM
    abc_emails.cid208 co
INNER JOIN abc_emails.def208 def ON def.eid = co.id
INNER JOIN abc_emails.my208 my ON my.eid = co.id AND my.my_id = 2765
GROUP BY Account_ID, User_ID, def_medium

And here is a sample of results from that query:

    Account_ID | User_ID | Medium  | Created
    1            447503     Email   2014-02-05 03:53:42
    1            679769     Banner  2014-05-30 05:55:29
    1000         6477       CPC     2007-01-22 10:03:03
    10000        26805      Organic 2008-07-28 01:34:05
    10000        151153     import  2011-06-16 06:57:41
    10000        151154             2011-06-16 06:57:42
    10000        151155             2011-06-16 06:57:42
    10000        151156             2011-06-16 06:57:42
    10000        151157     none    2011-06-16 06:57:42

So for account ID 1, User_ID 447503 looks like the first known contact so the medium should be Email. Account 1,000 seems to have only one user so use CPC. Account 10,000 has several users and the first known user is Organic medium.

So I would like to alter this query to create a result set like so:

Account_ID | User_ID | Medium  | Created
 1           447503     Email   2014-02-05 03:53:42
 1000        6477       CPC     2007-01-22 10:03:03
 10000       26805      Organic 2008-07-28 01:34:05

Account_ID would be distinct in the result set - no duplicates.

1. How would I do this
2. Is it possible to do this by leaving Created field out of the SELECT all together?

Answer 1

There are many ways to do it and some of them are

select t1.* from abc_emails t1
left join abc_emails t2 on t2.Account_ID = t1.Account_ID
and t1.Created > t2.Created
where t2.Account_ID is null;

OR

select t1.* from abc_emails t1
where not exists
(
  select 1 from abc_emails t2
  where t1.Account_ID = t2.Account_ID
  and t1.Created > t2.Created
)