I have a table with pk
and dept
columns:
pk dept
-------
27 A
29 A
30 B
31 B
33 A
I need to select the first consecutive group, that is the first successive set of rows all having the same dept
value when the table is ordered by pk
, i.e. the expected result is:
pk dept
-------
27 A
29 A
In my example there are 3 consecutive groups (AA, BB and A). The size of a group is unlimited (can be more than 2).
The following query should do what you want (I named your table tx):
The idea is to look for tuples such that no tuple with a lesser pk and a different department exists.