I have following data in postgres:
index | item |
------+--------
10 | aaa |
20 | aaa |
30 | bbb |
40 | aaa |
50 | aaa |
I would like to group it and get the minimum and maximum value. However, it only should group until next row is different item.
Expectation:
indexMin | indexMax | item |
---------+----------+----------
10 | 20 | aaa |
30 | 30 | bbb |
40 | 50 | aaa |
Can guide on this?
This is a gaps-and-islands problem, where you want to group together "adjacent" rows. Here, the simplest approach is to use the difference between row numbers to identify the groups:
select min(idx) min_idx, max(idx) max_idx, item
from (
select
t.*,
row_number() over(order by idx) rn1,
row_number() over(partition by item order by idx) rn2
from mytable t
) t
group by item, rn1 - rn2
order by min(idx)