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