CODEWITHSUNDEEP
sqlpostgresqlsql-order-by
aphrid
aphrid

Reputation: 599

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!

Upvotes: 2

Views: 8487

Answers (2)

Matt
Matt

Reputation: 14341

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.

Upvotes: 2

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

After sorting by col1 you want to sort by id or col2 depending on what's in col1. As it's ascending in one case and descending in the other, you can work with a minus sign:

select *
from table
order by
  col1 desc nulls last,
  case when col1 is null then col2 else -id end desc nulls last;

Upvotes: 0

Related Questions