MYSQL在联接语句中选择MAX日期

Question

我正在尝试返回记录编号的历史位置

我所拥有的是：

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

解析大约需要1分钟，并返回如下数据：

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

我真正希望看到的是像这样的查询中的数据：

SELECT l.location, t.transaction_id, t.date_modified FROM transactions as t

JOIN locations as l

ON l.location_id = t.location_id

JOIN received as r

ON r.received_id = t.received_id

WHERE t.received_id='1782'

ORDER BY t.date_modified DESC

哪个返回

T-E1A   67290   2013-05-29 13:58:26

T-E1A   67289   2013-05-29 13:58:26

ADJUST  67283   2013-04-26 11:33:54

ADJUST  67284   2013-04-26 11:33:54

ST10    67279   2013-04-26 09:52:41

ST10    67278   2013-04-26 09:52:13

ST10    67277   2013-04-26 09:50:58

ST10    67276   2013-04-26 09:50:20

SH3     67274   2013-04-26 09:49:39

第二个查询更好，但我真的只想显示每个记录ID和位置的最后修改时间。

有人可以看到我在做什么吗？感谢您的帮助。