0

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.

Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
gorrch
  • 521
  • 3
  • 16
  • 2
    Does this answer your question? [Selecting first row per group](https://stackoverflow.com/questions/10930347/selecting-first-row-per-group) – Stu Nov 10 '21 at 10:33
  • 1
    Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) – Charlieface Nov 10 '21 at 11:06

1 Answers1

2

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;
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52