How to give a select and bring the top 1 of each group?

Asked

Viewed 321 times

1

I have a table of processes. That is, each time a process is executed, it generates a new line. I want to give a select bringing the last execution of each process. Follows table example:

Id|ProcessoNome|Situação|DataInicio
1 |RPA_001     |sucesso |03/10/2018
2 |RPA_002     |sucesso |04/10/2018
3 |RPA_003     |erro    |04/10/2018
4 |RPA_003     |sucesso |04/10/2018
5 |RPA_001     |sucesso |04/10/2018
sql select-sql

  • What is the database manager (and version): Oracle Database? Mariadb? Postgresql? SQL Server?

2 answers

2

Hello,

You need the last execution of each process, right? Below is a query that solves this scenario.

select distinct nome_processo,
  (select max(data)
     from processos p2
    where p1.nome_processo = p2.nome_processo) as ULTIMA_EXECUCAO
  from processos p1;

Data example:

+----+---------------+-----------------+------------+
| id | nome_processo | status_processo | data       |
+----+---------------+-----------------+------------+
|  1 | RPA_001       | sucesso         | 2018-10-03 |
|  2 | RPA_002       | sucesso         | 2018-10-04 |
|  3 | RPA_003       | erro            | 2018-10-05 |
|  4 | RPA_003       | sucesso         | 2018-10-04 |
|  5 | RPA_001       | sucesso         | 2018-10-04 |
|  6 | RPA_002       | sucesso         | 2018-10-06 |
+----+---------------+-----------------+------------+

Query return :

+---------------+------------+---+
| nome_processo | ULTIMA_EXECUCAO|
+---------------+----------------+
| RPA_001       | 2018-10-04     |
| RPA_002       | 2018-10-06     | 
| RPA_003       | 2018-10-05     | 
+---------------+----------------+

Any doubt we’re there, good luck!

0

My suggestion is you order the data implementing in select.

SELECT *FROM tb_processos
ORDER BY data_inicio DESC;

The rest is ideal to handle directly in the code.

Browser other questions tagged sql select-sql

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