CODEWITHSUNDEEP
sqlpostgresql
Cauder
Cauder

Reputation: 2627

Row number when another column is not null

I'd like to add a row number when another column is not null

I have a column with a date value and another with numerics and I'd like to fill the row number when the numeric column is not null

I tried with case when but that didn't work

Upvotes: 0

Views: 1096

Answers (2)

Eray Balkanli
Eray Balkanli

Reputation: 7990

Alternative to Gordon's solution, you can try to work on the non-null data first then union the rest like below:

SELECT nmb,nmb2,ROW_NUMBER() OVER (ORDER BY nmb2 asc) AS idx
FROM t
WHERE nmb2 IS NOT NULL

UNION 

SELECT nmb,nmb2,NULL
FROM t
WHERE nmb2 IS null

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270513

Is this what you want?

select t.*,
       (case when col2 is not null
             then row_number() over (partition by col2 is not null order by date)
        end) as seqnum
from t;

This assigns a sequential row number to the non-NULL numeric values, ordered by the date column.

Upvotes: 3

Related Questions