Getting only the last item per state in PostgreSQL-CodePudding

I got a table of data which consists of several items that can have different states (Submitted, In Verification, Completed, ...). The item state are not unique but I am only interested in getting the first occurence of an item per ID and Status:

ID	Modified Date	Status	(Query should:)
123	01.01.2021 12:01	Submitted
123	02.01.2021 12:02	In Verification
123	02.01.2021 12:03	In Verification	(remove)
123	02.01.2021 12:04	In Verification	(remove)
123	03.01.2021 12:05	Completed
123	03.01.2021 12:06	Completed	(remove)
345	06.01.2021 12:07	Submitted
345	06.01.2021 12:08	Submitted	(remove)
345	04.01.2021 12:09	In Verification
345	04.01.2021 12:10	In Verification	(remove)
345	19.01.2021 18:11	Completed

I want to know when the first change happened to each state of the item. My expected result would be.

ID	Modified Date	Status
123	01.01.2021 12:01	Submitted
123	02.01.2021 12:02	In Verification
123	03.01.2021 12:05	Completed
345	06.01.2021 12:07	Submitted
345	04.01.2021 12:09	In Verification
345	19.01.2021 18:11	Completed

Is there any way to get the desired result in PostgreSQL?

CodePudding user response：

You can try to use ROW_NUMBER window function to keep the first row per ID and Status.

SELECT id,
       ModifiedDate,
       status
FROM (
    SELECT *,ROW_NUMBER() OVER(PARTITION BY ID,Status ORDER BY ModifiedDate) rn
    FROM T
) t1
WHERE rn = 1;

CodePudding user response：

You can use row_number to rank the rows, and then pick the most recent:

select
*
from (
select
id,
modified_date,
status,
row_number() over (partition by id,status order by modified_date DESC) as rn
from
<your table>
) 
where 
rn = 1

CodePudding user response：

with cte as (
Select 
    ID, 
    Status, 
    ModifiedDate, 
    ROW_NUMBER() OVER(Partition by ID,Status order by ModifiedDate) as RNUM
from 
    TableName
)
select
    ID, 
    Status, 
    ModifiedDate
from cte
where 
    RNUM = 1

CodePudding user response：

Simply GROUP BY:

select ID, min(ModifiedDate) FirstModifiedDate, Status
from tablename t
group by ID, Status
order by ID, FirstModifiedDate