I'm working with a table called du_vertrag, and I'm trying to build a query that selects distinct values for id and status pairs.
| id | status | date |
|---|---|---|
| 6251899 | beantragt | 20201008 |
| 6377042 | beantragt | 20201008 |
| 6387891 | beantragt | 20201008 |
| 6251899 | übergabe | 20201009 |
| 6377042 | übergabe | 20201009 |
| 6387891 | übergabe | 20201009 |
| 6251899 | übergabe | 20201010 |
| 6377042 | übergabe | 20201010 |
| 6387891 | übergabe | 20201010 |
| 6251899 | aktiv | 20201024 |
| 6377042 | aktiv | 20201024 |
| 6387891 | aktiv | 20201024 |
Desired Output:
| id | status | date |
|---|---|---|
| 6251899 | beantragt | 20201008 |
| 6377042 | beantragt | 20201008 |
| 6387891 | beantragt | 20201008 |
| 6251899 | übergabe | 20201009 |
| 6377042 | übergabe | 20201009 |
| 6387891 | übergabe | 20201009 |
| 6251899 | aktiv | 20201024 |
| 6377042 | aktiv | 20201024 |
| 6387891 | aktiv | 20201024 |
I need for every id the new status with the first datetime the status changed.
CodePudding user response：
This appears to be simple aggregation
select id, status, min(date) as date
from du_vertrag
group by id, status;
CodePudding user response：
SELECT * FROM (SELECT id, status, row_number() over (partition by id, status order by date asc) as row_num FROM du_vertrag ) d where d.row_num = 1