Olá pessoal
Preciso pegar o último registro de um serial levando em consideração que tem que pesquisar em todas essas tabelas com a seguinte nomenclatura, registers_2017_01, registers_2017_02 etc.
Tentei conforme abaixo
SELECT c.name,SUBSTRING(c.name_fantasy,1,30) AS name_fantasy,CONCAT(v.title,' ',v.plaque) AS title,v.type_vehicle,r.id,r.sn,r.lat,r.lng, r.direction,r.date_db,r.date_gps,r.volt_main,r.in1,r.in2,r.in3,r.speed,r.ign,r.sat_fix,r.sat,i.name_in1,i.name_in2,i.name_in3,i.name_in4,i.name_in5,i.name_in6,i.name_out1,i.name_out2,i.name_out3,i.name_out4,i.name_out5,i.name_out6
FROM clients c
JOIN vehicles v
ON c.id = v.clients_id
JOIN installations i
ON v.id = i.vehicles_id
JOIN trackers t
ON i.trackers_id = t.id
JOIN registers r
ON t.cod = r.sn
LEFT JOIN (
SELECT sn, MAX(id) AS id FROM (SELECT rr.sn, MAX(rr.id) AS id FROM registers rr GROUP BY sn
UNION ALL
SELECT rr.sn, MAX(rr.id) AS id FROM registers_2017_07 rr GROUP BY sn
UNION ALL
SELECT rr.sn, MAX(rr.id) AS id FROM registers_2017_08 rr GROUP BY sn
UNION ALL
SELECT rr.sn, MAX(rr.id) AS id FROM registers_2017_09 rr GROUP BY sn
UNION ALL
SELECT rr.sn, MAX(rr.id) AS id FROM registers_2017_10 rr GROUP BY sn
UNION ALL
SELECT rr.sn, MAX(rr.id) AS id FROM registers_2017_11 rr GROUP BY sn
UNION ALL
SELECT rr.sn, MAX(rr.id) AS id FROM registers_2017_12 rr GROUP BY sn
UNION ALL
SELECT rr.sn, MAX(rr.id) AS id FROM registers_2018_01 rr GROUP BY sn
UNION ALL
SELECT rr.sn, MAX(rr.id) AS id FROM registers_2018_02 rr GROUP BY sn) tabela GROUP BY sn
) AS L2 ON r.sn = L2.sn AND r.id = L2.id
AND c.management_id = 1
GROUP BY v.id;