how do I query sql for a latest record date and hour for each user

Question

I have a table that is a collection entries to the progress of law processes.

id_andamento_processo   id_processo data_andamento  hora_andamento  descricao
1   18      2016-05-30  14:00:00    
2   3122    2016-05-18  16:08:54    campo para o texto da PUBLICAÇÃO DO DIÁRIO OFICIAL
3   3122    2016-05-18  16:09:55    campo para o texto da PUBLICAÇÃO DO DIÁRIO OFICIAL
4   3122    2016-05-18  16:13:49    campo para o texto da PUBLICAÇÃO DO DIÁRIO OFICIAL
5   2001    2015-07-15  14:34:54    ANDAMENTO: 30/06/2014 Protocolo (E-Doc 12148046) 10003/2014 (LAT-Laudo Assistente Técnico): PROTOCOLO
6   2001    2015-07-15  14:34:54    ANDAMENTO: 03/07/2014 Protocolo (E-Doc 12174924) 10352/2014 (LPC-Laudo Pericial (Conhecimento)): PROTOCOLO
7   2001    2015-07-15  14:34:54    ANDAMENTO: 15/07/2014 Devolução de Carga

How do I create a query that would give me the latest date and hour for each process?

I have followed the aswer to this related question: how do I query sql for a latest record date for each user and I am able to get the latest date to each process.

select a.id_processo, a.data_andamento, a.hora_andamento, a.descricao_ptbr
from andamento_processo a
inner join (
    select id_processo, max(data_andamento) as Maxdata_andamento
    from andamento_processo
    group by id_processo
) am on a.id_processo = am.id_processo and a.data_andamento = am.Maxdata_andamento

However, this returns duplicate progress with the same date and different time:

id_processo     data_andamento  hora_andamento  descricao_ptbr
0   2016-06-09  11:03:00     
18  2016-06-21  11:01:00     
18  2016-06-21  11:12:00    Verificação teste para o RR juridico.
18  2016-06-21  11:37:00    Teste

I tried to do add a MAX on the hour of the progress, but didn't work, some progresses disappeared. I tried like this:

select a.id_processo, a.data_andamento, a.hora_andamento, a.descricao_ptbr
from andamento_processo a
inner join (
    select id_processo, max(data_andamento) as Maxdata_andamento,   max(hora_andamento) as Maxhora_andamento
from andamento_processo
group by id_processo
) am on a.id_processo = am.id_processo and a.data_andamento = am.Maxdata_andamento and a.hora_andamento = am.Maxhora_andamento

I am using MySQL. I need a query without duplicate progresses, like this:

id_processo     data_andamento  hora_andamento  descricao_ptbr
0               2016-06-09      11:03:00     
18              2016-06-21      11:37:00        Teste
1006            2016-05-25      16:10:10        Descrição: Mero expediente (19/05/16) Descrição: Mero expediente (19/05/16)

try concat( max(data_andamento),' ',max(hora_andamento)). Add that as an extra column — Claus, Dec 27 '16 at 14:41
Why are date and time in separate columns? Does that indicate that the two can refer to different things? Are they nullable? Can a missing date then mean that the time represents a daily re-occurring event? Maybe this is just bad table design and you would have to replace the date and time by one datetime. — Thorsten Kettner, Dec 27 '16 at 15:09
Whoever did the table design is not in the company anymore. Probably as you said, this is just a bad table design. Thanks for everything. — giancarloap, Dec 27 '16 at 17:05

Thorsten Kettner · Accepted Answer · 2016-12-27T16:57:22.753

1

It is not clear why date and time are two separate columns. Maybe because they can be null for certain scenarios. So get those records for which both are given (and presumably belong together), combine them to one datetime and work with this.

select 
  id_processo,
  max(timestamp(data_andamento, hora_andamento))
from andamento_processo
where data_andamento is not null
  and hora_andamento is not null
group by id_processo
order by id_processo;

If you want more data from the records with the max datetimes then use above as a subquery (derived table) and join the table again.

For completeness sake: Here the same query in an IN clause to get more data from the records:

select *
from andamento_processo
where (id_processo, timestamp(data_andamento, hora_andamento)) in
(
  select 
    id_processo,
    max(timestamp(data_andamento, hora_andamento))
  from andamento_processo
  where data_andamento is not null
    and hora_andamento is not null
  group by id_processo
)
order by id_processo;

edited Dec 27 '16 at 16:57

answered Dec 27 '16 at 15:16

Thorsten Kettner

89,309
7
49
73

Almost there. id_processo and max(timestamp(data_andamento, hora_andamento)) are perfect, but the column descricao_ptbr is coming wrong, instead of 'Teste', it comes another value from a register with previous date. I'm tryng to fix it. Any idea would be helpful. – giancarloap Dec 27 '16 at 16:28
Yes, of course, if you simply add `descricao_ptbr` to the select list without any aggregate function (such as `MIN`, `MAX` etc.) it defaults to `ANY_VALUE(descricao_ptbr)` as should be expected. This is why I told you, you would have to join above query with the table again to get more data from the records. (Or use the query in an `IN` clause, which I consider even cleaner.) – Thorsten Kettner Dec 27 '16 at 16:33
Well, I've added the query. – Thorsten Kettner Dec 27 '16 at 16:58
My bad. The query you added now is perfect! – giancarloap Dec 27 '16 at 17:03

score 0 · Answer 2 · answered Dec 27 '16 at 14:41

You can add data_andamento in the group by clause.

select a.id_processo, a.data_andamento, a.hora_andamento, a.descricao_ptbr
from andamento_processo a
inner join (
    select id_processo, max(data_andamento) as Maxdata_andamento,
    max(hora_andamento) as Maxhora_andamento
from andamento_processo
group by id_processo, data_andamento
) am on a.id_processo = am.id_processo and a.data_andamento =    
am.Maxdata_andamento and a.hora_andamento = am.Maxhora_andamento

Tim Biegeleisen · Answer 3 · 2016-12-27T14:48:24.790

0

select a.id_processo,
       a.data_andamento,
       a.hora_andamento,
       a.descricao_ptbr
from andamento_processo a
inner join
(
    select id_processo,
           max(data_andamento) as Maxdata_andamento
    from andamento_processo
    group by id_processo,
             date_format(data_andamento, '%Y-%m-%d %H')
) am
    on a.id_processo = am.id_processo and
       a.data_andamento = am.Maxdata_andamento

edited Dec 27 '16 at 14:48

answered Dec 27 '16 at 14:42

Tim Biegeleisen

502,043
27
286
360

how do I query sql for a latest record date and hour for each user

3 Answers3