Postgres SQL Group By Until Next Row Different

Question

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?

GMB · Accepted Answer · 2020-08-05 14:01:27Z

4

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)

answered Aug 5, 2020 at 14:01

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres SQL Group By Until Next Row Different

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related