SELECT MAX DATE for each ID

greatest-n-per-group, join, max, mysql

Solution

Using a sub query to get the max date / time for the historical record for each id, and using that to get the rest of the latest historical record:-

SELECT tipo_hh.id, tipo_hh.nombre, tipo_hh_historial.valor
FROM tipo_hh
INNER JOIN 
(
    SELECT id, MAX(STR_TO_DATE(CONCAT(fecha_cambio, hora_cambio), '%d/%m/%Y%k:%i:%s')) AS MaxDateTime
    FROM tipo_hh_historial
    GROUP BY id
) Sub1
ON tipo_hh.id = Sub1.id
INNER JOIN tipo_hh_historial
ON tipo_hh_historial.id = Sub1.id
AND STR_TO_DATE(CONCAT(fecha_cambio, hora_cambio), '%d/%m/%Y%k:%i:%s') = Sub1.MaxDateTime

SQL Fiddle:-

http://www.sqlfiddle.com/#!2/68baa/2

Problem

I have two calls this "tipo_hh" and "tipo_hh_historial". I need to make a join between the two tables, where "id" is the same in both tables. But I need that for each "id" in the table "tipo_hh" select the "valor" on the table "tipo_hh_historial" with the condition that is the record with "fecha_cambio" and "hora_cambio" maxima. "id" is primary key and auto increment in the table "tipo_hh" Something like this. This is the table "tipo_hh" ``` id nombre 1 Reefer 2 Lavados 3 Dry 4 Despacho ``` This is the table "tipo_hh_historial" ``` id valor fecha_cambio hora_cambio 1 1.50 27/06/2013 19:15:05 1 5.50 27/06/2013 19:19:32 1 5.50 27/06/2013 19:20:06 1 2.50 27/06/2013 21:03:30 2 4.66 27/06/2013 19:15:17 2 3.00 27/06/2013 19:20:22 3 5.00 27/06/2013 19:20:32 4 1.50 27/06/2013 19:20:50 ``` And I need this: ``` id nombre valor 1 Reefer 2.50 2 Lavados 3.00 3 Dry 5.00 4 Despacho 1.50 ```

Original source