MySQL JOIN table based on MAX(date) in main table, and MAX(id) in joined table with LIMIT -

if title didn't make sense here's need in nut shell.. need select recent x amount of records "by date" in main table, join data belongs records selecting recent record "by id" in joined table..

here's sample outputs..

table: lead_unique (only unique ssn's in table)

 +-----------+--------------+ | ssn       | created_date | +-----------+--------------+ | 111111111 | 2015-03-01   | | 999999999 | 2015-03-03   | | 555555555 | 2015-02-08   | +-----------+--------------+ 

table: lead_data

 +----+-----------+-------+----------------+-------------+-------+-------+ | id | ssn       | name  | address        | city        | state | zip   | +----+-----------+-------+----------------+-------------+-------+-------+ |  1 | 111111111 | bob1  | 1234 test ln   | mound       | ca    | 55555 | |  2 | 111111111 | bob2  | 1234 test ln   | mound       | ca    | 55555 | |  3 | 999999999 | jane1 | 5432 lola blvd | patton      | nj    | 33333 | |  4 | 999999999 | jane2 | 5432 lola blvd | patton      | nj    | 33333 | |  5 | 555555555 | jack1 | 832 92nd ave n | bright view | al    | 88888 | |  6 | 999999999 | jane3 | 5432 lola blvd | patton      | nj    | 33333 | +----+-----------+-------+----------------+-------------+-------+-------+ 

desired output (can asc/desc date column, don't matter)

 +--------------+-----------+-------+ | created_date | ssn       | name  | +--------------+-----------+-------+ | 2015-03-03   | 999999999 | jane3 | | 2015-03-01   | 111111111 | bob2  | | 2015-02-08   | 555555555 | jack1 | +--------------+-----------+-------+ 

desired output (limit 2)

 +--------------+-----------+-------+ | created_date | ssn       | name  | +--------------+-----------+-------+ | 2015-03-03   | 999999999 | jane3 | | 2015-03-01   | 111111111 | bob2  | +--------------+-----------+-------+ 

query might following may wayyy off since i'm here asking , having no luck..

 select        lead_unique.created_date,      lead_unique.ssn,     lead_data.name           lead_unique join           (         select                 ...                           lead_data         ...     ) lead_data          ... ... limit 2 

i have used stack overflow once before if there's more can add helpful please let me know! thanks!!

i inclined use correlated subquery data piece -- question mentions 1 column:

select u.created_date, u.ssn,        (select d.name         lead_data d         d.ssn = u.ssn         order d.id desc         limit 1        ) name lead_unique u order u.created_date desc limit 2; 

actually, performance reasons, put unique component in subquery:

select u.created_date, u.ssn,        (select d.name         lead_data d         d.ssn = u.ssn         order d.id desc         limit 1        ) name (select u.*       lead_unique u       order u.created_date desc       limit 2      ) u; 

edit:

even multiple columns, performant method might still use subqueries:

select created_date, ssn, d.* (select u.created_date, u.ssn,              (select d.id               lead_data d               d.ssn = u.ssn               order d.id desc               limit 1              ) id       (select u.*             lead_unique u             order u.created_date desc             limit 2            ) u      ) u join      lead_data d      on u.id = d.id; 

by way, if performance issue, want following indexes: lead_unique(created_date) , lead_data(id). should pretty fast 2 indexes.