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 |