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