Raizuri Raizuri - 28 days ago 11
SQL Question

SQL: Order by - sorting on another column in case of tie

My sample table

id | col1 | col2
---+------+-----
1 | 5 | 1
11| |
8 | 1 | 2
3 | | 1
4 | 1 | (where blanks are nulls)
6 | | 4
2 | 4 | 9
9 | 7 |
10| |


I'm trying to order by col1 in descending order (nulls last), and in the case of a tie (for example, rows (8, 1, 2) and (4, 1, )), I'd like to order it in ascending order by id.

In the case where the remaining values in col1 are null, I then sort by descending order of col2.

So my resulting table should look like this:

id | col1 | col2
---+------+-----
9 | 7 |
1 | 5 | 1
2 | 4 | 9
4 | 1 | (where blanks are nulls)
8 | 1 | 2
6 | | 4
3 | | 1
10| |
11| |


I'm having trouble with my query. I've tried doing the folllowing, but none of them seem to work properly.

/* This creates the correct ordering, but in the case of ties
they are ignored and don't follow id ascending */
select *
from table
order by
col1 desc nulls last,
col2 desc nulls last,
id asc;


-

/* When this finds a null value, it basically ignores the desc requirement of col 2 */
select *
from table
order by
col1 desc nulls last,
id asc,
col2 desc nulls last;


If it matters, I'm using PostgreSQL.

Any help would be greatly appreciated. Thanks!

Answer
SELECT *
FROM
    Table
ORDER BY
    Col1 DESC nulls last,
    ,CASE WHEN Col1 IS NOT NULL THEN Id END ASC
    ,Col2 DESC nulls last
    ,Id

The trick is to use a case expression to remove the ID value when Col1 is null so when you order by it it will treat all Ids where Col1 is null the same, but when col1 is not null it will participate in the ascending order by.