CODEWITHSUNDEEP
sqlsql-servercommon-table-expression
Tim
Tim

Reputation: 88

Is it possible to remove cascading CTEs in this query when trying to add ROW_NUMBER

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:

  1. Display completed books from oldest completed to newest
  2. Then display the currently defined reading list controlled by the "Status" column
  3. Then display any other books by that author not already listed

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

Answers (1)

eshirvana
eshirvana

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

Related Questions