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

CodePudding user response：

Yes, it could be done with window functions:

with cte1 as (
    select *, case when free = 1 and lag(free) over (order by marker) = 1 then 0 else 1 end as grp_change
    from item
), cte2 as (
    select *, sum(grp_change) over (order by marker) as grp_number
    from cte1
)
select min(marker), case when free = 1 then max(marker) end, free
from cte2
group by grp_number, free

It works as follows:

• Mark all rows that have free = 0 or (free = 1 and previous row free <> 1)
• Do a running sum over marked rows to create group numbers
• Group the data by group numbers

DB<>Fiddle