All geek questions in one place

MYSQL Select MAX Date inside the join operator

I am trying to return historical locations with record numbers

I have:

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

It takes about 1 minute for parsing and returns data such as:

 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 really expect to see is data such as from such a query:

 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

What returns

 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

This second query is better, but I really want to show only the last modified time for each identifier and location of the record.

Can anyone understand what I'm doing wrong? I appreciate the help.

+6
source share
2 answers

Something like that...

 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

whose core 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;
+10
source 
 SELECT l.location, t.transaction_id, max(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' group by l.location, t.transaction_id ORDER BY t.date_modified DESC

Does it help?

0
source

Source: https://habr.com/ru/post/950353/

More articles:


All Articles