Group contiguous rows in PostgreSQL

Question

Given this table:

| i | x | +---+---+ | 1 | A | | 2 | A | | 3 | B | | 4 | B | | 5 | B | | 6 | B | | 7 | A | | 8 | A | | 9 | A |

How can I group the rows that share the same letter (in column x) to get the following output?

| x | c | +---+---+ | A | 2 | | B | 4 | | A | 3 |

Additionally, I'd like to output the amount of rows that have been grouped (shown in column c).

I tried the following SQL query:

SELECT x, count(*) c FROM table_name GROUP BY x

This query though groups all rows with the same value and that is not what I want, because the result doesn't match my desired one. Both A-groups are grouped together into one instead of keeping them separate.

| x | c | +---+---+ | A | 5 | | B | 4 |

Is there any column can represent the order?

D-Shih
– D-Shih

2018-11-10 21:51:42 +00:00
Commented Nov 10, 2018 at 21:51 — D-Shih
– D-Shih, Commented Nov 10, 2018 at 21:51

D-Shih · Accepted Answer · 2018-11-10 22:12:27Z

This is a Gaps and Islands problem

Islands and gaps appear in all sorts of sequences, be they row number columns where some rows have been removed or dates that occur in sequence (but some are missing).

You can try to make grp column for the gap group by number in a subquery, then you can group by on grp and X columns in the main query and get count

select X,COUNT(grp) c from ( SELECT *, ROW_NUMBER() OVER(ORDER BY i) - ROW_NUMBER() OVER(PARTITION BY X ORDER BY i) grp FROM table_name ) t1 group by grp,X

sqlfiddle

Result

x c ===== A 2 B 4 A 3

Collectives™ on Stack Overflow

Group contiguous rows in PostgreSQL

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related