CODEWITHSUNDEEP
sqlnullsql-order-bymultiple-columns
Humppakäräjät
Humppakäräjät

Reputation: 1256

SQL-query order by multiple columns having null values

I got a table like this (Note: the real table has by far more columns):

street|zip
-----------
'b'   |0
'a'   |0
'c'   |null
'f'   |1
null  |0
null  |null

I would like to do a query like this:

SELECT * FROM locations ORDER BY street, zip

The result of this query would be this:

street|zip
-----------
'a'   |0
'b'   |0
'c'   |null
'f'   |1
null  |0
null  |null

Now the question is how must the query look like to produce this output (is it even possible?):

street|zip
-----------
'a'   |0
'b'   |0
'c'   |null
null  |0
'f'   |1
null  |null

If the value of a column is null it should be ignored/interpreted as a wildecard. I'm using PostgreSQL 9.2.

Thanks for your help.

Upvotes: 2

Views: 460

Answers (1)

Albin Sunnanbo
Albin Sunnanbo

Reputation: 47038

No, it does not look possible. You can't build a reproducible rule for that.

Your first example would also be valid if you treated null as a wild card.

street|zip
-----------
'a'   |0
'b'   |0
'c'   |null
null  |0
null  |null
'f'   |1

would be too.

Upvotes: 2

Related Questions