CODEWITHSUNDEEP
sqlpostgresqlpartition-by
justapilgrim
justapilgrim

Reputation: 6882

How to PARTITION BY a query accepting NULL values?

I have a query that I need to use PARTITION BY with 2 fields. I'm doing it like:

SELECT
    ROW_NUMBER()
    OVER (PARTITION BY
            id, country_id
        ORDER BY
            id, country_id) AS row_number
    FROM
        cities
    ...

The problem is that country_id, the second aggregated field used in PARTITION BY, can be NULL and I need ROW_NUMBER() to be summed up.

row_number() sums up only if there's a record with the same id and country_id in the result set. I want it to be summed up if there's a record with the same id and country_id or the same id and country_id is NULL.

Is there a way to do so?

Upvotes: 0

Views: 7397

Answers (3)

arce.est
arce.est

Reputation: 379

try REPLACE NULL

SELECT
ROW_NUMBER()
OVER (PARTITION BY
        id, ISNULL(country_id,-1)
    ORDER BY
        id, ISNULL(country_id,-1)) AS row_number
FROM
    cities
...

Upvotes: 1

Ankur Patel
Ankur Patel

Reputation: 1433

You can use the coalesce as shown below:

SELECT
    ROW_NUMBER()
    OVER (PARTITION BY
            id, Coalesce(country_id,0)) AS row_number
    FROM
        cities
    ...

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270713

It makes no difference if one of the values is NULL. On the other hand, it isn't useful to order by the same columns. In Postgres, the order by is optional, so you can just do:

row_number() over (partition by id, country_id)

Upvotes: 1

Related Questions