Search code examples
sqlsql-serverseqqtsql

Using If statement with a Row_Number then +1

I have tried creating the code below but it didn't work for me.

what I'm trying to do is use Row_number however check if the column MP =' Yes' then +1 to the Row_number value if it does otherwise just give me the Row_number.

  SELECT
 [Key]
,[Key2]
,MP
,OriginalOrder = ROW_NUMBER() OVER(Partition by [Key] ORDER BY  [Key],[Key2])
,IIF(MP = 'Yes' , ROW_NUMBER() OVER(Partition by [Key] ORDER BY  [Key],[Key2])+1,ROW_NUMBER() OVER(Partition by [Key] ORDER BY  [Key],[Key2]))what_i_have_tried
FROM
cte_RowNumbertest
ORDER BY
1,2

enter image description here

Solution

  • If I understand correctly, you can try to use SUM window function with CASE WHEN, if MP is yes add one more.

    SELECT
     [Key]
    ,[Key2]
    ,MP
    ,ROW_NUMBER() OVER(Partition by [Key] ORDER BY  [Key],[Key2]) OriginalOrder 
    ,SUM(CASE WHEN MP = 'Yes' THEN 2 ELSE 1 END) OVER(Partition by [Key] ORDER BY  [Key],[Key2]) what_i_have_tried
FROM
cte_RowNumbertest
ORDER BY
1,2