CASE with Row_Num

Question

I have a table in SQL Server 2016 where for every unique PartnerNumber there are multiple ConnectorId values and multiple CommissionDate

My requirement is when I query the table in the final result I should only get rows which are having rn = 1 this was working using the below Query, but in this example P1 as per current logic it picks a row where rn = 1 but what I additionally want is if CommissionDate for rn = 1 is NULL then in that case display value of rn = 2 if not null then continue with rn = 1

Table script:

CREATE table #Final_Data
(
  CommissionDate date,
  PartnerNumber varchar(50),
  Connector_Id varchar(50),
 )

GO

insert into #Final_Data (CommissionDate,PartnerNumber,Connector_Id)
VALUES (NULL,'P1','C1'), ('2017-12-27','P1','C2')
,('2015-09-14','P2','C3'),('2011-09-13','P2','C4') 
,(NULL,'P3','C5'),(NULL,'P3','C6') 

GO

Query:

;WITH CTE
AS
(
SELECT CommissionDate,PartnerNumber,Connector_Id,
ROW_NUMBER() OVER (Partition by PartnerNumber  ORDER BY CommissionDate asc) AS rn
FROM #Final_Data
)

SELECT TOP  9999999 * FROM CTE where rn = 1 
ORDER BY PartnerNumber

Actual Output:

CommissionDate  PartnerNumber   Connector_Id    rn
NULL                 P1              C1         1
2011-09-13           P2              C4         1
NULL                 P3              C5         1

Expected output:

CommissionDate  PartnerNumber   Connector_Id       rn
    2017-12-27           P1              C2         2
    2011-09-13           P2              C4         1
    NULL                 P3              C5         1

Answer 1

You can use a conditional sort in the order by clause of the window function:

ROW_NUMBER() OVER (
    PARTITION BY PartnerNumber  
    ORDER BY CASE WHEN CommissionDate IS NOT NULL THEN 0 ELSE 1 END,
    CommissionDate
) AS rn