How to order by max(a,b) in SQL?

Question

Consider the following table:

 id    a    b
--------------
 1     5    1
 2     2    3
 3     4    2
 4     3    6
 5     0    1
 6     2    2

I would like to order it by max(a,b) in descending order, so that the result will be:

 id    a    b
--------------
 4     3    6
 1     5    1
 3     4    2
 2     2    3
 6     2    2
 5     0    1

What will be the SQL query to perform such ordering ?

score 10 · Accepted Answer · answered Aug 11 '11 at 07:08

10

Use GREATEST :

SELECT *
FROM table
ORDER BY GREATEST(a, b) DESC

answered Aug 11 '11 at 07:08

Vincent Savard

2

Beware that both values must be not null. Example: select greatest(null,3) returns null. In that case you would have to use coalesce. select greatest(coalesce(null,0),coalesce(null,0)) this returns 0. – Nicola Cossu Aug 11 '11 at 07:28
1

@nick rulez: ah! mysql... you wonder why one would use it when it is so irrational. Thanks for mentioning it though, +1 – Vincent Savard Aug 11 '11 at 07:30
@Vincent: you're right. I don't understand the reason of this strange behaviour. :) – Nicola Cossu Aug 11 '11 at 07:33
@nick rulez: Thank for the `COALESCE` extension. In my case it is indeed needed because the rows in the table are a result of `LEFT JOIN`. By the way, is there any difference between `COALESCE` and `IFNULL` in MySQL ? – Misha Moroshko Aug 11 '11 at 07:37
1

`COALESCE` can deals with multiple values, whereas `IFNULL` deals with only two values – Cyril Gandon Aug 11 '11 at 08:17

1 Answers1