SELECT MAX DATE for each ID

Question

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

Answer 1

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

Answer 2

First of all you should use proper data types for your columns like for date there should a column of type data same as for the time column in you sample data set you have date formatted as '%d/%m/%Y' id this could be change to standard format '%Y-%m-%d' this will be good to so the below query is for proper types for the columns

SELECT t.* ,new_tipo_hh_historial.`valor`
FROM tipo_hh_new t
JOIN (
SELECT  th.*
FROM tipo_hh_historial_new th
JOIN (
SELECT id,valor,
 MAX(fecha_cambio ) fecha_cambio
,MAX(hora_cambio) hora_cambio
FROM `tipo_hh_historial_new` 
GROUP BY id
) thh 
ON (
th.`id` =thh.`id` 
AND th.fecha_cambio=thh.`fecha_cambio` 
AND th.hora_cambio = thh.`hora_cambio`
)
) new_tipo_hh_historial 
USING (id)

Fiddle Demo

And for in case you have date and time stored as string then you need to format them as real types you can use below query but not recommended

SELECT t.* ,new_tipo_hh_historial.`valor`
FROM tipo_hh t
JOIN (
SELECT  th.*
FROM tipo_hh_historial th
JOIN (
SELECT id,valor,
 MAX(STR_TO_DATE(fecha_cambio , '%d/%m/%Y')) fecha_cambio
,MAX(TIME_FORMAT(hora_cambio,'%H:%i:%s')) hora_cambio
FROM `tipo_hh_historial` 
GROUP BY id
) thh 
ON (
th.`id` =thh.`id` 
AND STR_TO_DATE(th.fecha_cambio , '%d/%m/%Y')=thh.`fecha_cambio` 
AND TIME_FORMAT(th.hora_cambio,'%H:%i:%s') = thh.`hora_cambio`
)
) new_tipo_hh_historial 
USING (id)

Fiddle Demo

Your problem seems like the greatest-n-per-group problem so you can first get the maxima from your table tipo_hh_historial maxima of fecha_cambio and hora_cambio and need to self join with multiple conditions to get the maximums like i.e

ON (
th.`id` =thh.`id` 
AND th.fecha_cambio=thh.`fecha_cambio` 
AND th.hora_cambio = thh.`hora_cambio`
)

and then join with your first table to get the expected results

Edit: the problem spotted by @Kickstart he already answered so i will provide the another way to overcome.There should be single field to store the date and time for the record like for fecha_cambio DATETIME so there will no chance to miss the id and get the correct maxima for date and time.See below updated query

SELECT t.* ,new_tipo_hh_historial.`valor`
FROM tipo_hh_new t
JOIN (
SELECT  th.*
FROM tipo_hh_historial_alter th
JOIN (
SELECT id,valor,
 MAX(fecha_cambio ) fecha_cambio
FROM `tipo_hh_historial_alter` 
GROUP BY id
) thh 
ON (
th.`id` =thh.`id` 
AND th.fecha_cambio=thh.`fecha_cambio` 
)
) new_tipo_hh_historial 
USING (id)

Updated fiddle demo