Dense_rank () order by and nulls - how to make it treat them like the bottom of the rating?

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 a test) that has a single bigint nullable column (called A). The table has the following data:

 NULL 1 2 

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

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

This returns:

 NULL,1 2,2 1,3 

I wonder if I configured exactly the same thing in SQL Server 2008 R2 and ran the same query that it returns:

 2,1 1,2 NULL,3 

So, I'm interested in who is right, but, more practically, I want the SQL Server behavior to be like how to get PostgreSQL to process zero at the bottom of the rating ?

(i.e. NULLS sorting is less than any value)

I noticed this on the dense_rank page, but this is not only about this function, but maybe this is the key?

Note. The SQL standard defines the RESPECT NULLS or IGNORE NULLS option for lead, lag, first_value, last_value, and nth_value. This is not implemented in PostgreSQL: the behavior always matches the behavior of the standard default value, namely RESPECT NULLS. Similarly, the FROM FIRST or FROM LAST standard for nth_value fails: only the default behavior of FROM FIRST is supported. (You can achieve the FROM LAST result by changing the order of ORDER BY.)

Answer 1

Use NULLS LAST to change the sort order of NULL values. Exactly what you asked for:

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

Not only for window functions, for ORDER BY anywhere.

Postgres does the right thing out of the box. Since NULL sorted in ascending order, it should start by default when the order is inverted.

on this topic:

• Sort PostgreSQL by asc date time, null first?