CODEWITHSUNDEEP
sql-servernullgaps-and-islands
eric
eric

Reputation: 147

Rank consecutive null values

I want to rank consecutive null value for my records. Every record will be rank as 1. For the null value that only appear once, the rank will also be 1. But for the null values that appear in a consecutive way, the rank will be 1 for the first record and 2 for the second record and so on. Here's my code.

CREATE TABLE #my_table
(
    id BIGINT                      IDENTITY PRIMARY KEY
    ,fruit                          varchar(100)
);

INSERT INTO #my_table
          SELECT 'apple'
UNION ALL SELECT 'apple'
UNION ALL SELECT NULL
UNION ALL SELECT 'pineapple'
UNION ALL SELECT 'banana'
UNION ALL SELECT  NULL
UNION ALL SELECT  NULL
UNION ALL SELECT 'orange'

select * from #my_table

Intended result

+----+-----------+------+
| id | fruit     | rank |
+----+-----------+------+
|  1 | apple     | 1    |
|  2 | apple     | 1    |
|  3 | NULL      | 1    |
|  4 | pineapple | 1    |
|  5 | banana    | 1    |
|  6 | NULL      | 1    |
|  7 | NULL      | 2    |
|  8 | orange    | 1    |
+----+-----------+------+

How should I query it?

Please help!

Upvotes: 2

Views: 309

Answers (3)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

Following solution doesn't use recursion (limited to 32767 level = ~ rows depending on solution) and also it uses only two agregate/ranking functions (SUM and DENSE_RANK):

;WITH Base
AS (
    SELECT  *, IIF(fruit IS NULL, SUM(IIF(fruit IS NOT NULL, 1, 0)) OVER(ORDER BY id), NULL) AS group_num
    FROM    @my_table t
)
SELECT  *, IIF(fruit IS NULL, DENSE_RANK() OVER(PARTITION BY group_num ORDER BY id), 1) rnk
FROM    Base b
ORDER BY id

Results:

id  fruit     group_num rnk
--- --------- --------- ---
100 apple     NULL      1
125 apple     NULL      1
150 NULL      2         1
175 pineapple NULL      1
200 banana    NULL      1
225 NULL      4         1
250 NULL      4         2
275 orange    NULL      1
300 NULL      5         1
325 NULL      5         2
350 NULL      5         3

Upvotes: 0

sapi
sapi

Reputation: 244

 CREATE TABLE #my_table
 (
     id BIGINT                      IDENTITY PRIMARY KEY
     ,fruit                          varchar(100)
 );

 INSERT INTO #my_table
           SELECT 'apple'
 UNION ALL SELECT 'apple'
 UNION ALL SELECT NULL
 UNION ALL SELECT 'pineapple'
 UNION ALL SELECT 'banana'
 UNION ALL SELECT  NULL
 UNION ALL SELECT  NULL
 UNION ALL SELECT 'orange'

 ;
 WITH REC_CTE (id,fruit,ranks)
     AS (
         -- Anchor definition
        SELECT  id,
                fruit,
                1 as ranks
        FROM #my_table
       WHERE fruit is not null

          -- Recursive definition
         UNION ALL
         SELECT son.id,
                son.fruit,
                case when  son.fruit is null AND father.fruit is null  then
                    father.ranks + 1
                    else
                    1
               end as ranks
         FROM #my_table son INNER JOIN
              REC_CTE father
         on son.id = father.id +1 
         WHERE son.fruit is null
          --AND father.fruit is null
     )

  SELECT * from REC_CTE  order by id

  DROP TABLE #my_table

Upvotes: 1

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can use difference of ROW_NUMBER to get the grouping of consecutive NULL values:

WITH Cte AS(
    SELECT *,
        g = ROW_NUMBER() OVER(ORDER BY id)
                - ROW_NUMBER() OVER(PARTITION BY fruit ORDER BY id)
    FROM #my_table  
)
SELECT 
    id,
    fruit,
    CASE
        WHEN fruit IS NULL THEN ROW_NUMBER() OVER(PARTITION BY fruit, g ORDER BY id)
        ELSE 1
    END AS rank
FROM Cte
ORDER BY id;

ONLINE DEMO

Upvotes: 1

Related Questions