SQL Server – Always Return X Rows with Variable Starting Row

ctepagingsql servert-sql

My goal is to return X rows from a table, where the value of X is some value in the low 100's. For the purposes of testing this query, I am experimenting with different X values.

This is a take on pagination for populating a grid in a user interface. I tried using Offset / Fetch in this query, but I could not get it to function with my @Partial variable.

@Partial is a string that will identify the starting row number and all row numbers that follow the first match. This is pretty simple until @Partial has returned the last row. In that instance, I want to return the last row plus the previous X – 1 rows.

My query appears to be functioning, but I feel that I'm over thinking the problem. I have done hours of testing and online research. I have provided a full working query that will execute.

When @Partial is null, then the query abides by the @StartRow and @NumWindowRows variables. Otherwise, @NumWindowRows will be returned, starting or ending with the matched string.

Thanks for any assistance on this query. To recap, I am getting my intended results, but I am looking for guidance on a simpler approach or method to improve efficiency.

/***
Dynamic variables
******/
DECLARE 
@StartRowNum INT = 1
,@NumWindowRows INT = 3
,@Partial NVARCHAR(10) = 'M';

/*****
Test table
*******/
DECLARE @Customers TABLE (
    CustomerID VARCHAR(10)
    ,[Name] VARCHAR(155)
    );
/***
Generate test data
*****/
INSERT INTO @Customers(CustomerID, [Name])
SELECT Char(number+65), 'Customer ' +Char(number+65) 
  FROM master.dbo.spt_values
 WHERE name IS NULL AND 
       number < 26

/***
Return @NumWindowRows
******/

;WITH FullResult AS (
        SELECT ROW_NUMBER() OVER (ORDER BY CustomerID) AS RowNum
        ,COUNT(*) OVER () AS FullCount
        ,CustomerID
        ,[Name]
        FROM @Customers),
PartialResult AS (SELECT *, COUNT(*) OVER () AS FullCount
        FROM 
        (SELECT ROW_NUMBER() OVER (ORDER BY CustomerID) AS RowNum
            ,CustomerID
            ,[Name]
         FROM @Customers) c
         WHERE CustomerID >= @Partial)

SELECT f.*
FROM FullResult f
LEFT OUTER JOIN PartialResult p ON f.RowNum = p.RowNum
WHERE (@Partial IS NULL AND f.RowNum >= @StartRowNum
        AND f.RowNum <= @NumWindowRows + @StartRowNum -1)
OR (f.RowNum >= CASE 
                WHEN (SELECT MAX(FullCount) FROM PartialResult) < @NumWindowRows
                THEN ((SELECT MIN(RowNum) FROM PartialResult) - (@NumWindowRows - (SELECT MAX(FullCount) FROM PartialResult)))
                ELSE (SELECT MIN(RowNum) FROM PartialResult)    END
        AND f.RowNum <= CASE 
                WHEN (SELECT MAX(FullCount) FROM PartialResult) > @NumWindowRows
                THEN ((SELECT MAX(RowNum) FROM PartialResult) - ((SELECT MAX(FullCount) FROM PartialResult) - @NumWindowRows))
                WHEN (SELECT MAX(FullCount) FROM PartialResult) + @StartRowNum < @NumWindowRows
                THEN f.FullCount
                ELSE (SELECT MAX(RowNum) FROM PartialResult) END
            )

Best Answer

Okay, so we need to find out how many rows are in the table, and how many rows are left once we've moved to the marker defined by @Partial.

-- simple variable to hold the offset:
DECLARE @offset int;

;WITH x(o,c) AS 
(
  SELECT 
    -- how many rows are at or past the @Partial marker?
    SUM(CASE WHEN CustomerID >= @Partial THEN 1 END),
    -- how many rows total?
    COUNT(*) 
  FROM @Customers
 ) 
 -- the magic: a conditional that determines if we're 
 -- too close to the end, and subtracts the difference
 -- between how close we are and how close we want to be
 SELECT @offset = (c-o) - CASE 
   WHEN @NumWindowRows > (c - (c-o)) THEN
       (@NumWindowRows - (c - (c-o))) 
   ELSE 0 END
FROM x;

SELECT 
  ROW_NUMBER() OVER (ORDER BY CustomerID),
  CustomerID, 
  Name
FROM @Customers
ORDER BY CustomerID
OFFSET @offset ROWS
FETCH NEXT @NumWindowRows ROWS ONLY;

You could probably do this all inline instead of using the @offset variable, but it gets messy very fast, and I don't think it's possible to avoid the additional scan required to derive those counts anyway.

This is far simpler than your logic. But it assumes that you will always order by CustomerID and never add additional filters. If there are less than @NumWindowRows rows in the table, well...

Related Question