GROUP BY last record

6

I'm doing a query in the database, 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

I need to bring the result by grouping the protocols by the last value of datahora ,

or so I need the result like this:

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
    
asked by Furlan 10.11.2014 в 18:47
source

3 answers

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.

    
answered by 10.11.2014 / 19:05
source
5

Just use the 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.

MAX and MIN example in MySql .

  

Note: I do not have mysql here to test, so there may be some syntax error. If so, let me know if I can.

    
answered by 10.11.2014 в 18:59
1

In MySQL you just sort your SELECT from the latest to the oldest and apply the 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 on all databases. For example, in Oracle it does not allow you to do SELECT of columns that are not in GROUP BY, so you would have to use WINDOW FUNCTIONS , but in MySQL this is possible.

    
answered by 10.11.2014 в 18:55