title: Is it possible to remove cascading CTEs in this query when trying to add ROW_NUMBER
body: <p>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.</p>
  <p>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 &quot;Ord&quot; in my query.</p>
  <p>I was able to get this working utilizing cascading Common Table Expressions but feel there must be a better way.</p>
  <p>Here is what the output should do when displaying all books by an author:</p>
  <ol>
  <li>Display completed books from oldest completed to newest</li>
  <li>Then display the currently defined reading list controlled by the &quot;Status&quot; column</li>
  <li>Then display any other books by that author not already listed</li>
  </ol>
  <p><strong>Setup</strong></p>
  <pre><code>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;
  </code></pre>
  <p><strong>Original Query</strong></p>
  <pre><code>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] &gt; 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
  </code></pre>
  <p>This is the only way I've been able to get this to work as expected.</p>
  <pre><code>;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] &gt; 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
  </code></pre>
  <p>Expected Output</p>
  <pre><code>|---------------------|-------------------------------------------|---------------------|------------------|-----------------------|------------------|---------------------|------------------|---------------------|------------------|
  |       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         |
  |---------------------|-------------------------------------------|---------------------|------------------|-----------------------|------------------|---------------------|------------------|---------------------|------------------|
  </code></pre>