GROUP BY last entry

Asked

Viewed 3,470 times

6

I’m making an appointment at the bank, follow my table below

id | protocolo | status | alteracao | datahora
1        2         1        teste     2014-11-10 15:23:44
2        2         3        teste     2014-11-10 14:23:44
3        2         4        teste     2014-11-10 13:23:44
4        1         2        teste     2014-11-10 09:23:44
5        1         3        teste     2014-11-10 10:23:44

need to bring the result grouping the protocols by the ultimovalor datahora,

or it is necessary the result so:

id | protocolo | status | alteracao | datahora
1        2         1        teste     2014-11-10 15:23:44
5        1         3        teste     2014-11-10 10:23:44
php mysql sql

  • I don’t understand @Furlan, can edit your question demonstrating how you want the query?

  • opa @Thiagothaison so a moment

  • @Thiagothaison altered my question and left as she has to return me

3 answers

5

Only use MAX and MIN operators;

SELECT min( t.id ),
       t.protocolo,
       min( t.status ),
       min( t.alteracao ),
       max( t.datahora )
  FROM tabela AS t
 GROUP BY t.protocolo;

Something similar to this in MYSQL.

Mysql MAX and MIN example.

Note: I don’t have mysql here to test, so there might be some syntax error. If there is, let me know that I fix it.

  • Valew @Fernando vc saved the day!

  • Cool @Furlan who helped, I only posted without references because I’m in the rush, the night I get an improved response and testo in mysql. hehe

  • I may be wrong, but it seems that this query returned the lowest values of each column, not the records with the highest date of each protocol.

  • Yes @Thiagothaison, I think you’re correct, but I don’t know if this is a problem for him. Plus what you quoted is true.

  • Valew @Fernando thanks so much for the help!

3


You can use the following query:

select * from tabela t where t.id = (select id from tabela where protocolo = t.protocolo order by datahora desc limit 0, 1);

In this way, the result will be as shown in the question.

  • her reply helped me a lot, I needed her, because she showed me what I wanted even without adapting to anything.

  • Yes @Furlan, I agree, after seeing the survey done by Thiago, I saw that my answer would help if the other values did not have to be of the same record. The answer that answers that question completely is is. + 1

1

In Mysql you just need to sort your SELECT from the latest to the oldest and apply GROUP BY. It will return the first line for each grouping.

SELECT id, protocolo, status, alteracao, datahora
FROM tabela
ORDER BY id, protocolo, status, datahora DESC
GROUP BY protocolo

Important to clarify that this does not work in all databases. For example, in Oracle it does not allow Voce to do SELECT of columns that are not in GROUP BY, then Voce would have to use WINDOW FUNCTIONS, but in Mysql this is possible.

  • you’re still returning more than one result with the same protocol

  • @Furlan I edited my answer. In fact the grouping has to be only by protocol.

Browser other questions tagged php mysql sql

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