Group by multiple fields and then fetch the most recent record

Table structure: create table test_tmp (
id int,
log_time datetime,
item varchar (10)
)
create table test_d (
id int,
name varchar (10)
)

Test data:
insert into test_d values (1, "hh");
insert into test_d values (2," xx");
insert into test_tmp values (1, "2018-08-16 16 hh" 13 hh" 36 cycles," a");
insert into test_tmp values (1, "2018-08-16 16 13 hh" 36 cycles," b");
insert into test_tmp values (1, "2017-08-16 16 13 hh" 36 cycles," a");
insert into test_tmp values (1, "2017-08-16 16 br 1336," b");
insert into test_tmp values (1, "2018-08-16 16 13 br 36," a");
insert into test_tmp values (2, "2018-08-16 16 br 1336," a");
insert into test_tmp values (2, "2018-08-16 16 br 1336," b");
insert into test_tmp values (2, "2017-08-16 16 br 1336," a");
insert into test_tmp values (2, "2017-08-16 16 13 br 36," b");
insert into test_tmp values (2, "2017-08-16 16 13 13 36," c");

I can only get all of what I write now. How can I get the item (separated by commas) of the most recent record (time) of each id?
select * from test_d d left join (select id, log_time, group_concat (item) from test_tmp group by id, log_time) t on d.id = t.id

clipboard.png

Mysql sql
Dec.08,2021

Let's take it step by step. First of all, the recent time of each test_tmp.id should be taken as follows: 

SELECT id, max(log_time) AS log_time
FROM test_tmp 
GROUP BY id;

secondly, according to the latest time, we can wrap another layer on the outside: 

SELECT
    t1.id, t1.log_time, group_concat(t1.item)
FROM test_tmp t1
INNER JOIN (
    SELECT id, max(log_time) AS log_time
    FROM test_tmp 
    GROUP BY id
) t2 ON t1.id = t2.id AND t1.log_time = t2.log_time
GROUP BY t1.id, t1.log_time;

in this way, we can find out which records correspond to the most recent time based on the most recent time, and on this basis, group_concat can find out the item of the most recent time.
and you can see that the above sql statement is not associated with the test_d table. If you need the id in the test_tmp to be the id, in the test_ d table, you can try it again.

Previous: Problems with yii2 associating queries using hasMay

Next: Mobile phone scrolling-how to achieve inertial scrolling

css mysql arrays josn react html typescript webpack npm sass R objective-c .net sql-server jquery python-3.x angularjs django angular excel regex iphone ajax linux xml pandas vba spring database wordpress string wpf xcode windows bash postgresql oracle multithreading eclipse list firebase algorithm macos forms image scala visual-studio azure bootstrap spring-boot react-native python-2.7 docker performance function winforms matlab powershell apache dataframe api sqlite numpy rest shell selenium flutter dart maven loops qt swing android-studio csv express file class tensorflow sorting codeigniter perl
© 2021 CodesHelper
Advertising Contact us About us
Menu