CODEWITHSUNDEEP
sql-server
Majesty Cherry Tomato
Majesty Cherry Tomato

Reputation: 181

How can I use Row Num partition by different default value

I have something like

ID Mobile isOptOut 
1  123     1
2  123     0
3  123     0
4  123     1
5  234     1
6  234     0

to have something like partition by mobile and isOptOut if the isOptOut is equal to 1 start from 0 otherwise start from 6

ID Mobile isOptOut RowNum
1  123    1        0 
4  123    1        1
2  123    0        6
3  123    0        7
5  234    1        0
6  234    0        6

select *,
case when isOptOut = 0 then ROW_Number() OVER(
    PARTITION BY Mobile ,isOptOut
    order by Mobile ,isOptOut
) as [Row Number]
from r
where isOptOut = 1

Thanks so much

Upvotes: 0

Views: 750

Answers (2)

Артем Карпец
Артем Карпец

Reputation: 84

SELECT ID,Mobile,isOptOut, IIF(isOptOut=1,ROWNUMBER-1,ROWNUMBER+5),ROWNUMBER FROM (
SELECT *, (ROW_Number() OVER(
    PARTITION BY Mobile,isOptOut ORDER BY ID, Mobile,isOptOut )) ROWNUMBER
    FROM  @table) as T ORDER BY ID

I think subquery and IIF suit you to reach your target. I created a window function and in the main query the condition.

Upvotes: 0

seanb
seanb

Reputation: 6685

You've on the right path with the window ROW_NUMBER function. However,

  • As you want to number all the rows, you'll need it for all rows (not just isOptOut = 0)
  • In the window function, I've ordered it by ID so it will always return the same values (if you order by the same fields as the partition, then they could come out in any order)
  • Once you have the row_numbers, add the modifier e.g., if isOptOut = 0, add 5 to the row number; or if it's 1, subtract 1
; WITH src AS
    (select *,
    ROW_Number() OVER(
        PARTITION BY Mobile, isOptOut
        ORDER BY ID -- Note I changed this to 'ID' for ordering
        ) as [rn]
    FROM r
    )
SELECT  ID, Mobile, isOptOut, 
        CASE isOptOut 
            WHEN 0 THEN rn + 5 
            WHEN 1 THEN rn - 1
            ELSE NULL 
            END AS RowNum
   FROM src

Upvotes: 3

Related Questions