Modifying a WITH CTE AS statement

November 12, 2021

Trying to figure out how to modify this WITH CTE AS statement. What I have below will update my StackCustomerAltID numericly with the customers in ABC order that are not blank and Delflg = 0. But I need to figure out how to modify it to update my StackCustomerAltID numericly, customers in ABC order with the blank customers. But the problem seems to be the blanks. I tried removing the where clause but it puts the blanks first then the ones that have actual customers in them.

Existing Query

WITH CTE AS(
    SELECT StackCustomerAltID,
           ROW_NUMBER() OVER (ORDER BY Customer) AS RN
    FROM dbo.StackCustomer Where DelFlg = 0 and Customer <> '')
UPDATE CTE
SET StackCustomerAltID = RN;

without where clause

WITH CTE AS(
    SELECT StackCustomerAltID,
           ROW_NUMBER() OVER (ORDER BY Customer) AS RN
    FROM dbo.StackCustomer )
UPDATE CTE
SET StackCustomerAltID = RN;

>Solution :

How about:

WITH CTE AS
(
    SELECT 
        StackCustomerAltID,
        ROW_NUMBER() OVER 
        (
            ORDER BY 
                CASE WHEN Customer Is Null THEN 1 ELSE 0 END, 
                Customer
        ) AS RN
    FROM 
        dbo.StackCustomer 
)
UPDATE CTE
SET StackCustomerAltID = RN;