sqlsql-serverpaginationkeyset-pagination

How to use keyset pagination?


Suppose I have total 800 eligible rows in database which is ordered by a column requisitionid in descending order. I want to display the records in 80 pages each page having 10 rows. We are using requisitionid as seek predicate. So the predicate should be less than or greater than? As the query will progress from UI (Angular + primeNG), I want to send just one parameter - requisitionid. If it is less than query, then the query will be SELECT ... FROM ... where requisitionid < ?, so here we are talking about first row. If we go for greater than query i.e. SELECT ... FROM ... where requisitionid > ?, here we are talking about last row of the page.

Please refer to Life without offset

EDIT Actual code:

with topten as (SELECT  DISTINCT TOP 10   
        REQN.CASE_ID
        ,userContact2.BV_First_Name + ' ' + userContact2.BV_Last_Name ReQCreater
        ,REQN.BV_Internal_Job_Title
        ,REQN.BV_Posted_Job_Title as postedJobTitle
        ,REQN.BV_Status
        ,REQN.BV_Taleo_Id
        ,REQN.BV_WD_PositionID
        ,jobcode.BV_Job_Code
        ,loc.BV_LocationCode
        ,loc.BV_LocationName
        ,D.BV_Division_Code AS 'divCode',

        ISNULL(loc.BV_Address1,'') + CASE WHEN ISNULL(loc.BV_Address1,'') = '' THEN '' ELSE ', ' END + ISNULL(loc.BV_Address2,'') + CASE WHEN ISNULL(loc.BV_Address2,'') = '' THEN '' ELSE ', ' END
        + ISNULL(loc.BV_City,'') + CASE WHEN ISNULL(loc.BV_City,'') = '' THEN '' ELSE ', ' END + ISNULL(loc.BV_State,'') + CASE WHEN ISNULL(loc.BV_State,'') = '' THEN '' ELSE (case when ISNULL(loc.BV_ZipCode,'') = '' THEN '' ELSE ', ' END) END
        + ISNULL(loc.BV_ZipCode,'') AS locationAddress
        from dbo.CW_V_REQN as REQN  
        INNER JOIN dbo.CW_TL_Requisition__Location_Master as reqLocLink on REQN.CASE_ID = reqLocLink.FROM_ID 
        INNER JOIN dbo.CW_V_LOCTMAST as loc on loc.CASE_ID = reqLocLink.TO_ID 
        INNER JOIN dbo.CW_TL_UserContactInfo__Location_Master as locUserLink on locUserLink.TO_ID =  loc.CASE_ID 
        INNER JOIN dbo.CW_V_USERCONT as userContact on userContact.CASE_ID = locUserLink.FROM_ID 
        LEFT JOIN dbo.CW_TL_Requisition__Department as reqDeptLink on REQN.CASE_ID = reqDeptLink.FROM_ID 
        INNER JOIN dbo.CW_V_DEPARTME as dept on dept.CASE_ID = reqDeptLink.TO_ID 
        LEFT  JOIN dbo.CW_TL_UserContactInfo__Department_Master as deptUserLink on dept.CASE_ID=deptUserLink.TO_ID 
        INNER JOIN dbo.CW_TL_Requisition__Job_Code as reqJobLink on REQN.CASE_ID = reqJobLink.FROM_ID 
        LEFT JOIN dbo.CW_V_JOBCODE as jobcode on jobcode.CASE_ID = reqJobLink.TO_ID 
        LEFT JOIN dbo.CW_V_USERCONT as userContact2 on (userContact2.BV_Login_Name = REQN.CREATED_BY) 
        LEFT JOIN CW_TL_LocationMaster__Division_Master LD ON (LD.FROM_ID = loc.CASE_ID) 
        LEFT JOIN CW_V_DIVISION D ON (D.CASE_ID = LD.TO_ID) 
        WHERE userContact.BV_Login_Name = @LOGINNAME

        AND REQN.CASE_ID < @MINCASEIDPREVPAGE
        ORDER BY REQN.CASE_ID DESC
        )

select topten.* , T.* from topten cross join (select min(case_id) as min from topten) as T


Solution

  • For key based pagination on a descending key, the WHERE clause predicate should be < for the next page and > for the previous page. Also, the ORDER BY clause for the previous page needs to be ASC (for the TOP predicate) along an outer DESC (for the descending key display sequence). Below is an example.

    --create test table with sample data
    CREATE TABLE dbo.YourTable(
        requisitionid int PRIMARY KEY
    );
    WITH 
         t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
        ,t1000 AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num  FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c)
    INSERT INTO dbo.YourTable WITH(TABLOCKX)
    SELECT num
    FROM t1000
    WHERE num <= 800;
    GO
    
    --query for first page (page 1) on descending key (returns 800-791)
    SELECT TOP(10) requisitionid
    FROM YourTable
    ORDER BY requisitionid DESC;
    GO
    
    --query for next page (page 2) on descending key (returns 790-781)
    DECLARE @LastRequisitionIdOnPage int = 791;
    SELECT TOP(10) requisitionid
    FROM YourTable
    WHERE requisitionid < @LastRequisitionIdOnPage
    ORDER BY requisitionid DESC;
    GO
    
    --query for previous page (page 1) on descending key (returns 800-791)
    DECLARE @FirstRequisitionIdOnPage int = 790;
    SELECT requisitionid
    FROM (
        SELECT TOP(10) requisitionid
        FROM YourTable
        WHERE requisitionid > @FirstRequisitionIdOnPage
        ORDER BY requisitionid ASC
        ) AS prev_page
    ORDER BY requisitionid DESC;;
    GO