Postgres: Order by Non Zeros ASC followed by Zeros and then NULLS LAST

Question

I have a PG database table prices. The structure is as below:

id    name    total_sales    created_at
1      A         0.0         2016-01-01
2      B         1.25        2016-01-01
3      C         8.17        2016-01-01
4      D         15.09       2016-01-01
5      E         0.0         2016-01-01
6      F         NULL        2016-01-01
7      G         2.25        2016-01-01
8      H         19.34       2016-01-01
9      I         47.91       2016-01-01
10     J         0.0         2016-01-01
11     K         NULL        2016-01-01
12     L         0.01        2016-01-01
13     M         5.11        2016-01-01
14     N         27.53       2016-01-01
15     O         3.53        2016-01-01

What I need is very simple. I would like to order the records such that:

Items with values > 0.0 in ASCENDING order comes first followed by Items with 0.0 and then NULLS LAST

In short, I would need the o/p in below sequence:

1st: 12 => 0.01
2nd: 2 => 1.25,
3rd: 7 => 2.25,
4th: 15 => 3.53,
5th: 13 => 5.11,
6th: 3 => 8.17,
7th: 4 => 15.09,
8th: 8 => 19.34,
9th: 14 => 27.53,
10th: 9 => 47.91,
11th, 12th, 13th all 0.0
14th, 15th all NULLS

So, far I tried below SQLs but none worked!

SELECT * FROM prices
ORDER BY CASE WHEN total_sales = 0.0 THEN 0 ELSE total_sales END ASC NULLS LAST

Answer 1

order by total_sales = 0 nulls last, total_sales

false orders before true

Answer 2

You have three classes of values here:

1. positive values
2. zeroes
3. nulls

This can be expressed in a case expression, and you could use a secondary ordering to sorting the positive values in ascending order:

SELECT   *
FROM     prices
ORDER BY CASE WHEN total_sales > 0 THEN 1 
              WHEN total_sales = 0 THEN 2
              WHEN total_sales IS NULL THEN 3 -- Just for readability
         END ASC,
         total_sales ASC