Search code examples
sqlcoalescesql-order-by

How to order by 2 columns combining COALESCE?

I have a question about ordering a SQL table. And I can't find a solution on stack or google. My table "Score" seems as follows:

Name    Total   Tries  Game1  Game2  Game3
------------------------------------------
Sam     65      61     10     31     24             
Tom             55     11            30
Jim     65      58     9      34     22 
Dan     62      52     10     30     22

Note: "Total" column is COUNT(Game1 + Game2 + Game3).

As you can see the Total record of Tom is empty, because Tom didn't play Game2.

I want to order my table as follows (highest-lowest priority):

  1. Empty cells (at the bottom of the table)
  2. Total (ASC)
  3. Tries (ASC)

So my table has to look like:

Name    Total   Tries  Game1  Game2  Game3
------------------------------------------
Dan     62      52     10     30     22
Jim     65      58     9      34     22 
Sam     65      61     10     31     24             
Tom             55     11            30

Which SQL query do I have to use?

This is what I have on this moment, but it will not sort on "Tries":

SELECT name, Game1+Game2+Game3 AS Total, Game1, Game2, Game3, Tries
FROM Score 
ORDER BY CASE WHEN Tries or Game1 or Game2 or Game3 IS NULL THEN 0 ELSE 1 END DESC, 
         COALESCE(Game1,0) + COALESCE(Game2,1) + COALESCE(Game3,2) ASC
Solution

  • Considering you have NULL instead of empty in Total based on the CASE statement in your order by. Try this Order by

    ORDER BY COALESCE(Total,99999999999) ASC,Tries ASC

    99999999999 will make the NULL values to sort at the end and the other records will be ordered in ascending order

    or 

    ORDER BY case when Total is null then 0 else 1 end desc,Total ASC,Tries ASC