Get field related to higher date

Navigation
0

Yesterday the staff helped me get the last status referring to the highest date, ie displaying the status of the last modification.

Now I came across another difficulty related to the same issue but with multiple statuses I return not only the last date but the status for that last date.

example: 

// TABELA STATUS
(Numero: 10507, Status: Aguardando, Data: 2015-04-11 09:48:19),
(Numero: 10508, Status: Aguardando, Data: 2015-04-11 11:48:19),
(Numero: 10507, Status: Confirmado, Data: 2015-04-12 10:50:58),
(Numero: 10508, Status: Bloqueado, Data: 2015-04-12 11:50:58),
(Numero: 10507, Status: Despachado, Data: 2015-04-15 15:50:58), // RESULTADO PRETENDIDO
(Numero: 10508, Status: Confirmado, Data: 2015-04-15 16:50:58); // RESULTADO PRETENDIDO

// FILTRO
SELECT
numero.id,
numero.numero,
status.status,
MAX(status.data) AS data
FROM numero
INNER JOIN dados ON numero.id = dados.id_numero
INNER JOIN status ON 
(numero.numero = status.numero AND status.status = 'Confirmado') OR
(numero.numero = status.numero AND status.status = 'Despachado')
WHERE dados.data BETWEEN '2015-04-10' AND '2015-04-10 23:59:59.997'
GROUP BY numero.numero
ORDER BY dados.data

// RESULTADO DO FILTRO
|   id  |   numero  |   status      |   data    |
|   4   |   10507   |   Confirmado  |   April, 15 2015 15:50:58 |
|   5   |   10508   |   Confirmado  |   April, 15 2015 16:50:58 |

// RESULTADO PRETENDIDO
|   id  |   numero  |   status      |   data    |
|   4   |   10507   |   Despachado  |   April, 15 2015 15:50:58 |
|   5   |   10508   |   Confirmado  |   April, 15 2015 16:50:58 |

DEMO: SQL Fiddle

    
asked by anonymous 30.04.2015 / 15:31

1 answer

1

Return MAX (status.status) 

SELECT
numero.id,
numero.numero,
MAX(status.status),
MAX(status.data) AS data
FROM numero
INNER JOIN dados ON numero.id = dados.id_numero
INNER JOIN status ON 
(numero.numero = status.numero AND status.status = 'Confirmado') OR
(numero.numero = status.numero AND status.status = 'Despachado')
WHERE dados.data BETWEEN '2015-04-10' AND '2015-04-10 23:59:59.997'
GROUP BY numero.numero
ORDER BY dados.data

This only works because, luckily, "Dispatched" is greater than "Confirmed" as both string and its stream.

DEMO

    
30.04.2015 / 15:45
The animation of the menu buttons is not working correctly Android - ListView with image via url, how to add one?