CODEWITHSUNDEEP
sqlpostgresqlnullsql-order-byamazon-redshift
ocwirk
ocwirk

Reputation: 1089

Implementing NULLS FIRST in Amazon Redshift

I am using sum window function for row number, similar to this query -

SELECT field_a,
       SUM(1) OVER (PARTITION BY field_b ORDER BY field_c ASC ROWS UNBOUNDED PRECEDING) AS row_number
FROM test_table
    ORDER BY row_number;

The problem is that if field_c is a null value, it appears at the end. I want it at the beginning, so null value is treated as smaller than all other values. In Oracle, this could be done by providing NULLS FIRST argument, but its not supported in Redshift. So how do I implement it in Redshift ?

Upvotes: 1

Views: 3416

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656331

I want it [null] at the beginning, so null value is treated as smaller than all other values.

Use the expression 

field_c IS NOT NULL

as first ORDER BY item. It evaluates to ...
FALSE .. if NULL
TRUE .. if NOT NULL.

And FALSE (0) sorts before TRUE (1). Works for any data type and any possible distribution of values.

SELECT field_a,
       row_number() OVER (PARTITION BY field_b
                          ORDER BY field_c IS NOT NULL, field_c) AS row_number
FROM   test_table
ORDER  BY row_number;

Upvotes: 8

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125204

If field_c is string

order by coalesce(field_c, '')

If numeric

order by coalesce(field_c, 0)

Replace the zero with the lowest possible value

Upvotes: 1

Related Questions