Search code examples
sqlsql-serverrankpartitionrow-number

ROW_Number with Custom Group

I am trying to have row_number based on custom grouping but I am not able to produce it.

Below is my Query

CREATE TABLE mytbl (wid INT, id INT)
INSERT INTO mytbl Values(1,1),(2,1),(3,0),(4,2),(5,3)

Current Output

wid     id
1        1
2        1
3        0
4        2
5        3

Query

SELECT *, RANK() OVER(PARTITION BY wid, CASE WHEN id = 0 THEN 0 ELSE 1 END ORDER BY ID)
FROM mytbl

I would like to rank the rows based on custom condition like if ID is 0 then I have start new group until I have non 0 ID.

Expected Output

wid     id  RN
1        1  1
2        1  1
3        0  1
4        2  2
5        3  2
Solution

  • Guessing here, as we don't have much clarification, but perhaps this:

    SELECT wid,
       id,
       COUNT(CASE id WHEN 0 THEN 1 END) OVER (ORDER BY wid ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) +1 AS [Rank]
FROM mytbl ;