MYSQL Select MAX Date inside a join statement

Question

I'm trying to return a record number's historic locations What I have is: ``` SELECT l.location, t.transaction_id, t.date_modified FROM transactions as t INNER JOIN ( SELECT t1.received_id, t1.transaction_id, t1.date_modified FROM ( SELECT received_id, MAX(date_modified) as maxmodify FROM transactions GROUP BY received_id) as max_record JOIN transactions as t1 ON (t1.received_id =max_record.received_id) ) as whatever INNER JOIN locations as l ON l.location_id = t.location_id INNER JOIN received as r ON r.received_id = t.received_id WHERE t.received_id='1782' ORDER BY t.date_modified DESC ``` This takes about 1 min to parse and returns data like: ``` T-E1A 67294 2013-05-29 14:05:30 T-E1A 67293 2013-05-29 14:05:30 T-E1A 67294 2013-05-29 14:05:30 T-E1A 67293 2013-05-29 14:05:30 T-E1A 67294 2013-05-29 14:05:30 T-E1A 67293 2013-05-29 14:05:30 T-E1A 67294 2013-05-29 14:05:30 ``` What I'm really expecting to see is data like from a query like this: ``` SELECT l.location, t.transaction_id, t.…

Accepted Answer

Something like this...

```
SELECT t1.received_id
     , t1.transaction_id
     , t1.date_modified
     , l.location
  FROM transactions t1
  JOIN ( SELECT received_id, MAX(date_modified) maxmodify FROM transactions GROUP BY received_id) max_record
    ON max_record.received_id = t1.received_id 
   AND max_record.maxmodify = t1.date_modified
  JOIN locations l
    ON l.location_id = t1.location_id
  JOIN received r
    ON r.received_id = t1.received_id
 WHERE t1.received_id = '1782'
 ORDER 
    BY t1.date_modified DESC
```

the kernel of which is this...

```
SELECT x.*
  FROM my_table x
  JOIN (SELECT id,MAX(thing) max_thing FROM my_table GROUP BY id) y 
    ON y.id = x.id 
   AND y.max_thing = x.thing;
```

MYSQL Select MAX Date inside a join statement

Solution

Problem