CODEWITHSUNDEEP
sql-server-2008paginationsql-order-bycase
Timbob
Timbob

Reputation: 35

Pagination in SQL SELECT with ORDER BY based on CASE generated columns - TSQL

I am using MS SQL SERVER 2008 and I have created the following query (simplified);

SELECT DATE_A, DATE_B
CASE
WHEN DATEDIFF(day, DATE_A, GETDATE()) >= 1 THEN 1
WHEN DATEDIFF(day, DATE_B, GETDATE()) >= 1 AND DATEDIFF(day, DATE_A, GETDATE()) <1 THEN             2
ELSE 3
END AS sortPriority,
CASE
--deadline past
WHEN DATEDIFF(day, DATE_A, GETDATE()) >= 1 THEN DATE_A
--review only past
WHEN DATEDIFF(day, DATE_B, GETDATE()) >= 1 AND DATEDIFF(day, DATE_A()) <1 DATE_B
--anything else
ELSE DATE_B
END AS sortDate
FROM myTable
WHERE (DATEDIFF(day, DATE_A, GETDATE()) >=1 OR DATEDIFF(day, DATE_B, GETDATE()) >=1)
ORDER BY sortPriority, sortDate;

The query returns rows where DATE_A or DATE_B are older than todays date. The rows are sorted by sortPriority and then by sortDate.

I now need to add pagination to this query, however when I use the sortPriority or sortDate columns in the order by clause of the ROW_NUMBER() function, the query fails;

WITH sortedTable AS
(
SELECT DATE_A, DATE_B,
CASE
WHEN DATEDIFF(day, DATE_A, GETDATE()) >= 1 THEN 1
WHEN DATEDIFF(day, DATE_B, GETDATE()) >= 1 AND DATEDIFF(day, DATE_A, GETDATE()) <1 THEN 2
ELSE 3
END AS sortPriority,
CASE
--deadline past
WHEN DATEDIFF(day, DATE_A, GETDATE()) >= 1 THEN DATE_A
--review only past
WHEN DATEDIFF(day, DATE_B, GETDATE()) >= 1 AND DATEDIFF(day, DATE_A()) <1 DATE_B
--anything else
ELSE DATE_B
END AS sortDate,
ROW_NUMBER() OVER (sortPriority, sortDate) AS 'RowNumber'
FROM myTable
WHERE (DATEDIFF(day, DATE_A, GETDATE()) >=1 OR DATEDIFF(day, DATE_B, GETDATE()) >=1)
)
SELECT * 
FROM sortedTable 
WHERE RowNumber BETWEEN 10 AND 20;

I get the following error messages;

Msg 207, Level 16, State 1, Line 24
Invalid column name 'sortPriority'.
Msg 207, Level 16, State 1, Line 24
Invalid column name 'sortDate'.

And the line number refers to this line of my sample code;

ROW_NUMBER() OVER (sortPriority, sortDate) AS 'RowNumber'

How can I approach this and get the desired result (pagination with original sorting intact)

Upvotes: 0

Views: 434

Answers (1)

Nitin Midha
Nitin Midha

Reputation: 2268

UNTESTED:

WITH sortedTable AS
(
    SELECT DATE_A, DATE_B, sortPriority, sortDate,
           ROW_NUMBER() OVER (sortPriority, sortDate) AS 'RowNumber'
      FROM
          (
            SELECT DATE_A, DATE_B,
                   CASE WHEN DATEDIFF(day, DATE_A, GETDATE()) >= 1 THEN 1
                        WHEN DATEDIFF(day, DATE_B, GETDATE()) >= 1 AND DATEDIFF(day, DATE_A, GETDATE()) <1 THEN 2
                        ELSE 3
                    END AS sortPriority,
                   CASE
                        --deadline past
                        WHEN DATEDIFF(day, DATE_A, GETDATE()) >= 1 THEN DATE_A
                        --review only past
                        WHEN DATEDIFF(day, DATE_B, GETDATE()) >= 1 AND DATEDIFF(day, DATE_A()) <1 DATE_B
                        --anything else
                        ELSE DATE_B
                    END AS sortDate
               FROM myTable
              WHERE (DATEDIFF(day, DATE_A, GETDATE()) >=1 OR DATEDIFF(day, DATE_B, GETDATE()) >=1)
          ) T
)
SELECT * 
FROM sortedTable 
WHERE RowNumber BETWEEN 10 AND 20;

Upvotes: 2

Related Questions