Skip to content
Advertisement

Can row_number() ignore null in oracle

I have data like this

---------------------------
|   code    | other column
---------------------------
|   C       |    a
|   null    |    a
|   A       |    a
|   null    |    a
|   null    |    a
----------------------------

How can i write query to get row_number without counting null column.

----------------------------------
| id  |   code    | other column |
----------------------------------
| 1   |   C       |    a
|     |   null    |    a
| 2   |   A       |    a
|     |   null    |    a
|     |   null    |    a
----------------------------------

Advertisement

Answer

Well, not specifically. But you can get what you want by using conditional logic:

select (case when code is not null
             then row_number() over (partition by (case when code is not null then 1 else 0 end)
                                     order by . . .
                                    )
        end) as id

It is not clear to me what the order by is for the row_number() which is what the . . . means.

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement