Postgres SQL Group By Until Next Row Different

user_9962568 0
Viewed 171

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?

gaps-and-islands max min postgresql sql
1 Answers

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)
user_106767160
Related