Take only first row from sets of rows-CodePudding

I have table like below. Normally it has more columns, but for simplicity I attached most important here. Rows with the same ItemId are almost the same. Sometimes ts is different for flag equals to 0 or 1. Not always there are two rows for one ItemId, but they are always ordered like below (flag asc). I want to always fetch only 1 row, but if there are both flags, I want to take with Flag=0.

FROM:

ItemId	Flag	ts
x	0	2021-01-01 02:00
x	1	2021-01-01 03:00
y	0	2021-01-01 03:00
y	1	2021-01-01 02:00
z	1	2021-01-01 01:00
w	1	2021-01-01 01:00

TO:

ItemId|ts
------|-----------------
x     |2021-01-01 02:00
y     |2021-01-01 03:00
z     |2021-01-01 01:00
w     |2021-01-01 01:00

I tried with MAX, but it can not be used cause there is no logic for ts, so dates would be wrong.

CodePudding user response：

You can use row_number() :

select *
from(select t.*, row_number() over(partition by itemid order by flag) as seq
     from table t
    ) t
where seq = 1;