CODEWITHSUNDEEP
sql
brandon
brandon

Reputation: 595

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.

Upvotes: 0

Views: 176

Answers (2)

Vitaliy Smolyakov
Vitaliy Smolyakov

Reputation: 472

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

Upvotes: 1

JBrooks
JBrooks

Reputation: 10013

Should look something like this:

DECLARE @limit int = 25, @offset int = 10

DECLARE @ret TABLE (
ID INT,
TYPEID INT)

INSERT INTO @ret
SELECT Id, TypeId     
FROM Logs
WHERE TypeId = 1
ORDER BY id
LIMIT @limit OFFSET @offset;


INSERT INTO @ret
SELECT id, TypeId     
FROM Logs
WHERE TypeId = 2
AND ID BETWEEN (SELECT MAX(ID) FROM @ret) AND (SELECT MIN(ID) FROM @ret)

SELECT *
FROM @ret
ORDER BY ID

You could collapse this into a single SQL statement using a UNION, but it would have to query for the same data (limit and offset) more than once.

Upvotes: 1

Related Questions