Get field for longest date

Asked

Viewed 1,527 times

0

Yesterday the guys helped me get the last status for the longest date, that is to display the status of the last modification.

Now I have arisen another difficulty related to the same question but with multiple status return me not only the last date but the status referring to 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

mysql

1 answer

1

Return the MAX(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, lucky for you, "Dispatched" is bigger than "Confirmed" as much as string as in the sequence of its flow.

DEMO

  • really works! but would not have otherwise, not counting with "my luck"?

  • Suddenly I can’t find another.

  • You have, for the same numero.numero, two records that meet the criterion, one with status = Confirmado and another with status = Despachado how you want to bring only the biggest can’t find any other way but MAX(status.status) .

  • If you change the clause GROUP BY of SQL of your question to GROUP BY numero.numero, status.status will see that 3 records are brought with the dates correctly assigned.

  • I found another way, take a look and tell us what you think :D SQL Fiddle

  • It is better, since what you want is to get the status with the longest date. Another thing, although Mysql does not oblige you, it is good practice to include in the clause ORDER BY all the (nonaggregated) columns which are indicated in SELECT, this will avoid situations like the one where your query returned a status = Confirmada with the status date Shipped.

  • Tendi, Rigadão @ramaral

Show 2 more comments

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.