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.
Advertisement
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.
x
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