I’m not quite sure how to describe this scenario, which is probably why I have difficulties googling for it. I can’t even say, if something like this is even possible. All I found, which goes into the right direction, would be window functions, however I can’t figure out how to apply it in this case.

I have a table that looks something like this (plus some more field irrelevant to the question):

CREATE TABLE item (marker VARCHAR(1), free TINYINT(1));

INSERT INTO item VALUES
('A', 1),
('B', 1),
('C', 0),
('D', 1),
('E', 1),
('F', 1),
('G', 0),
('H', 1),
('I', 0),
('J', 0);

I’d like to know how to write a query (considering an ORDER BY marker) that

  • displays all rows with free = 0 as they are
  • and combines all consecutive rows with free = 1 into a single row with information which range of rows have been combined.

So the result could be something like

marker last_marker free
A B 1
C null 0
D F 1
G null 0
H H 1
I null 0
J null 0