Reputation: 88
I have a reading list in a database. I use this as a base for personal training It has grown in functionality over time, so please forgive the odd structure.
I'm trying to add paging to this query but I'm having an issue trying to add the ROW_NUMBER method because of how I implemented "Ord" in my query.
I was able to get this working utilizing cascading Common Table Expressions but feel there must be a better way.
Here is what the output should do when displaying all books by an author:
Setup
CREATE TABLE #books(
ID int,
Author nvarchar(100),
Title nvarchar(100),
Series nvarchar(100),
BookNumber int,
DateCompleted smalldatetime,
[Status] int
)
INSERT INTO #books (ID, Author, Title, Series, BookNumber, DateCompleted, Status) VALUES
(1004,'J. K. Rowling','Harry Potter and the Chamber of Secrets','Harry Potter',2,'1-Mar-2020',1),
(1045,'J. K. Rowling','Harry Potter and the Prisoner of Azkaban','Harry Potter',3,NULL,2),
(1047,'J. K. Rowling','Harry Potter and the Goblet of Fire','Harry Potter',4,NULL,3),
(1048,'J. K. Rowling','Harry Potter and the Order of the Pheonix','Harry Potter',5,NULL,4),
(1049,'J. K. Rowling','Harry Potter and the Half Blood Prince','Harry Potter',6,NULL,NULL),
(1051,'J. K. Rowling','Harry Potter and the Deathly Hallows','Harry Potter',7,NULL,NULL),
(1185,'J. K. Rowling','Harry Potter and the Sorcerer''s Stone','Harry Potter',1,'1-Jan2020',NULL)
DECLARE @Author nVarChar(100) = 'J. K. Rowling'
DECLARE @Year nVarChar(5) = ''
DECLARE @PageNum INT = 1;
DECLARE @PageSize INT = 10;
Original Query
SELECT b.ID,
b.Title,
b.Series,
b.BookNumber,
ISNULL(b.Series, '') + ISNULL(' (book ' + LTRIM(STR(b.BookNumber)) + ')','') as SeriesDesc,
b.Author,
Case
When b.[Status] = 1 Then 'Completed'
When b.[Status] = 2 Then 'Reading'
When b.[Status] > 2 Then 'Next'
End as StatusDesc,
b.[Status],
b.DateCompleted,
CASE
WHEN b.DateCompleted is not NULL THEN 0
WHEN b.[Status] is not NULL THEN 5
WHEN b.[Status] is NULL THEN 9
END as Ord
FROM #books b
WHERE (((b.Author = @Author OR @Author = '') AND b.DateCompleted is not NULL) AND
(Year(b.DateCompleted) = @Year OR @Year = ''))
OR
(b.Author = @Author AND b.DateCompleted is NULL AND @Year = '')
ORDER BY Ord, [Status], DateCompleted, Series, BookNumber
This is the only way I've been able to get this to work as expected.
;WITH Step1 AS
(SELECT b.ID,
b.Title,
b.Series,
b.BookNumber,
ISNULL(b.Series, '') + ISNULL(' (book ' + LTRIM(STR(b.BookNumber)) + ')','') as SeriesDesc,
b.Author,
Case
When b.[Status] = 1 Then 'Completed'
When b.[Status] = 2 Then 'Reading'
When b.[Status] > 2 Then 'Next'
End as StatusDesc,
b.[Status],
b.DateCompleted,
CASE
WHEN b.DateCompleted is not NULL THEN 0
WHEN b.[Status] is not NULL THEN 5
WHEN b.[Status] is NULL THEN 9
END as Ord
FROM #books b
WHERE (((b.Author = @Author OR @Author = '') AND b.DateCompleted is not NULL) AND
(Year(b.DateCompleted) = @Year OR @Year = ''))
OR
(b.Author = @Author AND b.DateCompleted is NULL AND @Year = '')
),
Step2 AS
(SELECT ID, Title, Series, BookNumber, SeriesDesc, Author, StatusDesc, [Status], DateCompleted, Ord,
ROW_NUMBER() OVER (ORDER BY Ord, [Status], DateCompleted, Series, BookNumber) as RowNum
FROM Step1)
SELECT
ID, Title, Series, BookNumber, SeriesDesc, Author, StatusDesc, [Status], DateCompleted, Ord, RowNum
FROM Step2
WHERE @PageNum = 0 OR (RowNum BETWEEN (@PageNum - 1) * @PageSize + 1
AND @PageNum * @PageSize)
ORDER BY RowNum
Expected Output
|---------------------|-------------------------------------------|---------------------|------------------|-----------------------|------------------|---------------------|------------------|---------------------|------------------|
| ID | Title | Series | BookNumber | SeriesDesc | Author | StatusDesc | Status | DateCompleted | Ord |
|---------------------|-------------------------------------------|---------------------|------------------|-----------------------|------------------|---------------------|------------------|---------------------|------------------|
| 1185 | Harry Potter and the Sorcerer's Stone | Harry Potter | 1 | Harry Potter (book 1) | J. K. Rowling | NULL | NULL | 2020-01-01 00:00:00 | 0 |
| 1185 | Harry Potter and the Chamber of Secrets | Harry Potter | 2 | Harry Potter (book 2) | J. K. Rowling | Completed | 1 | 2020-03-01 00:00:00 | 0 |
| 1185 | Harry Potter and the Prisoner of Azkaban | Harry Potter | 3 | Harry Potter (book 3) | J. K. Rowling | Reading | 2 | NULL | 5 |
| 1185 | Harry Potter and the Goblet of Fire | Harry Potter | 4 | Harry Potter (book 4) | J. K. Rowling | Next | 3 | NULL | 5 |
| 1185 | Harry Potter and the Order of the Pheonix | Harry Potter | 5 | Harry Potter (book 5) | J. K. Rowling | Next | 4 | NULL | 5 |
| 1185 | Harry Potter and the Half Blood Prince | Harry Potter | 6 | Harry Potter (book 6) | J. K. Rowling | NULL | NULL | NULL | 9 |
| 1185 | Harry Potter and the Deathly Hallows | Harry Potter | 7 | Harry Potter (book 7) | J. K. Rowling | NULL | NULL | NULL | 9 |
|---------------------|-------------------------------------------|---------------------|------------------|-----------------------|------------------|---------------------|------------------|---------------------|------------------|
Upvotes: 1
Views: 45
Reputation: 24633
then how about offset fetch :
SELECT b.ID,
b.Title,
b.Series,
b.BookNumber,
ISNULL(b.Series, '') + ISNULL(' (book ' + LTRIM(STR(b.BookNumber)) + ')','') as SeriesDesc,
b.Author,
Case
When b.[Status] = 1 Then 'Completed'
When b.[Status] = 2 Then 'Reading'
When b.[Status] > 2 Then 'Next'
End as StatusDesc,
b.[Status],
b.DateCompleted,
CASE
WHEN b.DateCompleted is not NULL THEN 0
WHEN b.[Status] is not NULL THEN 5
WHEN b.[Status] is NULL THEN 9
END as Ord
FROM #books b
WHERE (((b.Author = @Author OR @Author = '') AND b.DateCompleted is not NULL) AND
(Year(b.DateCompleted) = @Year OR @Year = ''))
OR
(b.Author = @Author AND b.DateCompleted is NULL AND @Year = '')
ORDER BY Ord, [Status], DateCompleted, Series, BookNumber
OFFSET @PageSize*@PageNum ROWS
FETCH NEXT @PageSize ROWS ONLY
@PageNum should start from 0
Upvotes: 1