I am trying to implement a paging routine that's a little different.
For the sake of a simple example, let's assume that I have a table defined and populated as follows:
DECLARE @Temp TABLE
(
ParentId INT,
[TimeStamp] DATETIME,
Value INT
);
INSERT INTO @Temp VALUES (1, '1/1/2013 00:00', 6);
INSERT INTO @Temp VALUES (1, '1/1/2013 01:00', 7);
INSERT INTO @Temp VALUES (1, '1/1/2013 02:00', 8);
INSERT INTO @Temp VALUES (2, '1/1/2013 00:00', 6);
INSERT INTO @Temp VALUES (2, '1/1/2013 01:00', 7);
INSERT INTO @Temp VALUES (2, '1/1/2013 02:00', 8);
INSERT INTO @Temp VALUES (3, '1/1/2013 00:00', 6);
INSERT INTO @Temp VALUES (3, '1/1/2013 01:00', 7);
INSERT INTO @Temp VALUES (3, '1/1/2013 02:00', 8);
TimeStamp
will always be the same interval, e.g. daily data, 1 hour data, 1 minute data, etc. It will not be mixed.
For reporting and presentation purposes, I want to implement paging that:
TimeStamp
pageSize
(say 4), but will automatically adjust to include additional records matching on TimeStamp
. In other words, if 1/1/2013 01:00 is included for one ParentId
, the suggested pageSize
will be overridden and all records for hour 01:00 will be included for all ParentId's
. It's almost like the TOP WITH TIES
option. So running this query with pageSize
of 4 would return 6 records. There are 3 hour 00:00 and 1 hour 01:00
by default, but because there are more hour 01:00's
, the pageSize
would be overridden to return all hour 00:00
and 01:00
.
Here's what I have so far, and I think I'm close as it works for the first iteration, but sequent queries for the next pageSize+
rows doesn't work.
WITH CTE AS
(
SELECT ParentId, [TimeStamp], Value,
RANK() OVER(ORDER BY [TimeStamp]) AS rnk,
ROW_NUMBER() OVER(ORDER BY [TimeStamp]) AS rownum
FROM @Temp
)
SELECT *
FROM CTE
WHERE (rownum BETWEEN 1 AND 4) OR (rnk BETWEEN 1 AND 4)
ORDER BY TimeStamp, ParentId
The ROW_NUMBER ensures the minimum pageSize is met, but the RANK will include additional ties.
WITH TIES might cause more rows to be returned than the value specified in expression. For example, if expression is set to 5 but two additional rows match the values of the ORDER BY columns in row 5, the result set will contain seven rows.
Example - Using TOP PERCENT keyword For example: SELECT TOP(10) PERCENT employee_id, last_name, first_name FROM employees WHERE last_name = 'Anderson' ORDER BY employee_id; This SQL Server SELECT TOP example would select the first 10% of the records from the full result set.
The OFFSET FETCH clause allows you to skip N first rows in a result set before starting to return any rows. In this syntax: The ROW and ROWS , FIRST and NEXT are the synonyms. Therefore, you can use them interchangeably.
Example 1: SQL Server TOP Clause with a constant value In this example, we retrieve the top 10 records from a SQL table without specifying any condition and ORDER BY Clause. Let's filter the output and retrieve only relevant columns. We can specify the required column names in the select statement.
declare @Temp as Table ( ParentId Int, [TimeStamp] DateTime, [Value] Int );
insert into @Temp ( ParentId, [TimeStamp], [Value] ) values
(1, '1/1/2013 00:00', 6),
(1, '1/1/2013 01:00', 7),
(1, '1/1/2013 02:00', 8),
(2, '1/1/2013 00:00', 6),
(2, '1/1/2013 01:00', 7),
(2, '1/1/2013 02:00', 8),
(3, '1/1/2013 00:00', 6),
(3, '1/1/2013 01:00', 7),
(3, '1/1/2013 02:00', 8);
declare @PageSize as Int = 4;
declare @Page as Int = 1;
with Alpha as (
select ParentId, [TimeStamp], Value,
Rank() over ( order by [TimeStamp] ) as Rnk,
Row_Number() over ( order by [TimeStamp] ) as RowNum
from @Temp ),
Beta as (
select Min( Rnk ) as MinRnk, Max( Rnk ) as MaxRnk
from Alpha
where ( @Page - 1 ) * @PageSize < RowNum and RowNum <= @Page * @PageSize )
select A.*
from Alpha as A inner join
Beta as B on B.MinRnk <= A.Rnk and A.Rnk <= B.MaxRnk
order by [TimeStamp], ParentId;
EDIT: An alternative query that assigns page numbers as it goes, so that next/previous page can be implemented without overlapping rows:
with Alpha as (
select ParentId, [TimeStamp], Value,
Rank() over ( order by [TimeStamp] ) as Rnk,
Row_Number() over ( order by [TimeStamp] ) as RowNum
from @Temp ),
Beta as (
select ParentId, [TimeStamp], Value, Rnk, RowNum, 1 as Page, 1 as PageRow
from Alpha
where RowNum = 1
union all
select A.ParentId, A.[TimeStamp], A.Value, A.Rnk, A.RowNum,
case when B.PageRow >= @PageSize and A.TimeStamp <> B.TimeStamp then B.Page + 1 else B.Page end,
case when B.PageRow >= @PageSize and A.TimeStamp <> B.TimeStamp then 1 else B.PageRow + 1 end
from Alpha as A inner join
Beta as B on B.RowNum + 1 = A.RowNum
)
select * from Beta
option ( MaxRecursion 0 )
Note that recursive CTEs often scale poorly.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With