SQL paging with non-fixed-length page size

The table is simple: It has 2 columns, id and type. The type can be 1 or 2.
Now I want the first 10 entries with type 1, in a sequence ordered by id, and if there are type 2 entries somewhere in between then they should be included in the result set too. What is the SQL query?

It’s basically paging but the number of rows per page can vary so can’t use OFFSET and LIMIT.

Answer

For SQL Server you can use CTE to make request for type 1 records once and then union with records of Type 2.

I selected only 5 rows to use less test data.

create table #TEST (Id int, Type int)
insert into #TEST
select  1,2 union
select  2,1 union
select  3,1 union
select  4,2 union
select  5,2 union
select  6,1 union
select  7,1 union
select  8,2 union
select  9,1 union
select  10,1 union
select  11,2 union
select  12,2 union
select  13,1 union
select  14,1 union
select  15,1

go

with list1 (Id, Type)
as 
(
    select *
    from #Test
    where Type = 1
    order by Id
    offset 0 rows fetch next 5 rows only
)

select * 
from list1 

union all 

select * 
from #Test
where Type = 2 and Id > (select min(Id) from list1) and Id < (select max(Id) from list1)
order by Id