dense_rank() order by and nulls - how to make it treat them as bottom of the ranking?

Question

I am using Postgresql version 9.1.9:

select version();
"PostgreSQL 9.1.9 on armv7l-unknown-linux-gnueabihf,
 compiled by gcc (Debian 4.6.3-14+rpi1) 4.6.3, 32-bit"

and I have a simple table (called Test) that has a single nullable bigint column (called A). The table has the following data:

NULL
1
2

Now I want to create a dense ranking (therefore using the dense_rank() function) so I do the following query:

select "A", dense_rank() over (order by "A" desc) from public."Test"

This returns:

NULL,1
2,2
1,3

Interestingly if I set up exactly the same thing in SQL Server 2008 R2 and run the same query it returns:

2,1
1,2
NULL,3

So, I am interested in who is correct but, more practically, what I want is the SQL Server behavior so, how can I make PostgreSQL treat the null as bottom of the ranking?

(i.e. sort NULLS as smaller than any value)

I noticed this on the dense_rank page, but it is not talking specifically about this function, but perhaps it is a clue?

Note: The SQL standard defines a RESPECT NULLS or IGNORE NULLS option for lead, lag, first_value, last_value, and nth_value. This is not implemented in PostgreSQL: the behavior is always the same as the standard's default, namely RESPECT NULLS. Likewise, the standard's FROM FIRST or FROM LAST option for nth_value is not implemented: only the default FROM FIRST behavior is supported. (You can achieve the result of FROM LAST by reversing the ORDER BY ordering.)

Answer 1

Use the NULLS LAST clause to modify how NULL values are sorted. Does exactly what you asked for:

SELECT "A", dense_rank() OVER (ORDER BY "A" DESC NULLS LAST)
FROM   public."Test"

Not just for window functions, for ORDER BY anywhere.

Postgres does the right thing out of the box. Since NULL sorts last in ascending order, it should come first by default when the order is inverted.

Related:

• PostgreSQL sort by datetime asc, null first?