CODEWITHSUNDEEP
sql
mustafa00
mustafa00

Reputation: 891

How to skip NULL (or 0) when using Row_Number?

I need to return the sequential number of a row based on values in ColValue. I need to do it until NULL occurs in a row, then start to count from the beginning. My original table is in the left picture, what I need is on the right (column RowNr):

This is what I have now: [This is what I need:2

I tried various combinations of ROW_NUMBER but in vain. Instead of NULL I can also set 0 or other value but the issue is how to start counting from the beginning right after this value.

One of my tries is here:

    SELECT journalized_id, ColValue,
        CASE WHEN ColValue IS NULL THEN NULL ELSE ROW_NUMBER() OVER(PARTITION BY CASE WHEN ColValue IS NULL THEN 1 ELSE 0 END ORDER BY ColValue) END AS RowNr
    FROM TableX

Do you have any idea how to solve it?

Upvotes: 0

Views: 2901

Answers (2)

Steve Lovell
Steve Lovell

Reputation: 2564

@mustafa00, I believe @GordonLinoff's answer is essentially correct. The main issue is that he has the colvalue field performing two roles which are incompatible. An understandable problem given how the question was presented. You need to find another field (or set of fields), to play one of those roles. Without that the question cannot be answered.

SELECT
    SubQuery.SomeOtherField,
    SubQuery.Journalized_Id,
    SubQuery.ColValue,
    SubQuery.Grp, --So you can see what it outputs
    CASE
        WHEN SubQuery.ColValue IS NOT NULL
        THEN ROW_NUMBER() OVER (PARTITION BY SubQuery.Grp ORDER BY SubQuery.SomeOtherField)
    END - (CASE WHEN SubQuery.Grp = 0 THEN 0 ELSE 1 END) AS RowNr 
          --A minor correction to Gordon's code to ensure each sequence starts at 1.
FROM
    (
    SELECT
        t.SomeOtherField,
        t.Journalized_Id,
        t.ColValue,
        SUM(CASE WHEN t.ColValue IS NULL THEN 1 ELSE 0 END)
            OVER (ORDER BY t.SomeOtherField) AS Grp
    FROM
        MyTable t
    ) SubQuery

The SubQuery is creating groups for each set of records requiring it's own number sequence starting at 1. It does this by counting the number of NULL values in ColValue which come before the current record. It can't do this unless we can give the query some means of understanding which records are really before the others, and to do that you need SomeOtherField (or set of fields) by which to order the records.

It is likely that there is some primary key to your table that can play the role of SomeOtherField.

This solution is demonstrated with your data plus SomeOtherField in this SQL Fiddle.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270513

Use a subquery to count the number of NULL values after a given row. That assigns the non NULL values a grouping. Then use row_number():

select x.*,
       (case when colvalue is not null
             then row_number() over (partition by grp order by colvalue)
        end) as seqnum
from (select x.*,
             sum(case when colvalue is null then 1 else 0 end) over (order by colvalue) as grp 
      from tablex x
     ) x

Upvotes: 0

Related Questions