MySQL Query Optimization LEFT JOIN and DISTINCT

Question

I have two table in MySQL :

system

Id_system   Name        Type
------------------------------
1           'System1'   'Type1'
2           'System2'   'Type1'
3           'System3'   'Type1'
4           'System4'   'Type2'

measures

Id_system   Sensor  Value   Insert_date
--------------------------------------------------
1           'S1'    12      '2019-23-04 15:16:05'
1           'S2'    2       '2019-23-04 15:16:05'
1           'S3'    42      '2019-23-04 15:16:05'
2           'S1'    11      '2019-23-04 15:11:05'
2           'S2'    3       '2019-23-04 15:11:05'
2           'S3'    43      '2019-23-04 15:11:05'
4           'S1'    2       '2019-23-04 15:10:05'
4           'S2'    32      '2019-23-04 15:10:05'
4           'S3'    2       '2019-23-04 15:10:05'
3           'S1'    32      '2019-23-04 15:06:05'
3           'S2'    54      '2019-23-04 15:06:05'
3           'S3'    15      '2019-23-04 15:06:05'
1           'S1'    10      '2019-23-04 15:00:05'
1           'S2'    1       '2019-23-04 15:00:05'
1           'S3'    35      '2019-23-04 15:00:05'

I would like have as result, the last measures for each systems, like this :

Id_system   Sensor  Value   Insert_date             Name        
---------------------------------------------------------------
1           'S1'    12      '2019-23-04 15:16:05'   'System1'
1           'S2'    2       '2019-23-04 15:16:05'   'System1'
1           'S3'    42      '2019-23-04 15:16:05'   'System1'
2           'S1'    11      '2019-23-04 15:11:05'   'System2'
2           'S2'    3       '2019-23-04 15:11:05'   'System2'
2           'S3'    43      '2019-23-04 15:11:05'   'System2'
3           'S1'    32      '2019-23-04 15:06:05'   'System3'
3           'S2'    54      '2019-23-04 15:06:05'   'System3'
3           'S3'    15      '2019-23-04 15:06:05'   'System3'

I tried this request in sql :

SELECT DISTINCT m.sensor, s.name, m.value, m.date_insert 
FROM system s 
LEFT JOIN measure m ON s.id_system = m.id_system 
WHERE s.type = 'Type1'

but she returns, each measures for each systems, while I just want the last measure of each type for each system :

Id_system   Sensor  Value   Insert_date             Name        
---------------------------------------------------------------
1           'S1'    12      '2019-23-04 15:16:05'   'System1'
1           'S2'    2       '2019-23-04 15:16:05'   'System1'
1           'S3'    42      '2019-23-04 15:16:05'   'System1'
2           'S1'    11      '2019-23-04 15:11:05'   'System2'
2           'S2'    3       '2019-23-04 15:11:05'   'System2'
2           'S3'    43      '2019-23-04 15:11:05'   'System2'
3           'S1'    32      '2019-23-04 15:06:05'   'System3'
3           'S2'    54      '2019-23-04 15:06:05'   'System3'
3           'S3'    15      '2019-23-04 15:06:05'   'System3'
1           'S1'    10      '2019-23-04 15:00:05'   'System1'
1           'S2'    1       '2019-23-04 15:00:05'   'System1'
1           'S3'    35      '2019-23-04 15:00:05'   'System1'

Do you have an idea of how to do it ?

I use MySQL : `mysql Ver 14.14 Distrib 5.7.25, for Linux (x86_64) using EditLine wrapper` — T.Launay, Apr 24 '19 at 08:42
Because `system4` is type of `type2` and I want the element of `type1` in `system` table — T.Launay, Apr 24 '19 at 08:44
The result i would like is last measures for each system which is type `type1` — T.Launay, Apr 24 '19 at 08:46

score 0 · Accepted Answer · answered Apr 24 '19 at 08:46

0

It is a typical greatest per group problem

select m.id_system, m.sensor, m.value, m.insert_date, s.name, s.type
join measures m
join system s on m.id_system = s.id_system
from (
    select m.id_system, m.sensor, max(m.insert_date) max_insert_date
    from measures m
    join system s on m.id_system = s.id_system and s.type = 'Type1'
    group by m.id_system, m.sensor
) t on t.id_system = m.id_system and 
       t.sensor = m.sensor and
       t.max_insert_date = m.insert_date
where s.type = 'Type1'

answered Apr 24 '19 at 08:46

Radim Bača

10,646
1
19
33

Thank you, your approach works with some modification. I allow myself to comment the right query to obtain result. Thanks again. – T.Launay Apr 24 '19 at 09:10
`SELECT m.id_system, m.sensor, m.value, m.insert_date, s.name, s.type FROM measure m JOIN system s ON m.id_system = s.id_system JOIN ( SELECT m.id_system, m.sensor, max(m.insert_date) as max_insert_date FROM measure m JOIN system s ON m.id_system = s.id_system AND s.type = 'GROWLIGHT' GROUP BY m.id_system, m.id_capteur ) t ON t.id_system = m.id_system AND t.sensor = m.sensor AND t.max_insert_date = m.insert_date WHERE s.type = 'GROWLIGHT'` – T.Launay Apr 24 '19 at 09:10

MySQL Query Optimization LEFT JOIN and DISTINCT

1 Answers1