How to order SQL queries with conditionals?

Question

I need help with it.

I have a table with the next data

|---id---|-----name-----|--value--|
|    1   |     Alex     |   300   |
|    2   |     John     |   800   |
|    3   |     Mary     |   0     |
|    4   |     Carl     |   100   |
|    5   |     Jesus    |   0     |
|    6   |     Aron     |   0     |

To order the table by value, I'm using:

SELECT * FROM table ORDER_BY value DESC;

But sometimes I get a result like:

|---id---|-----name-----|--value--|
|    2   |     John     |   800   |
|    1   |     Alex     |   300   |
|    4   |     Carl     |   100   |
|    5   |     Jesus    |   0     |
|    3   |     Mary     |   0     |  -- !
|    6   |     Aron     |   0     |

I want to order the table with a condition: "if value is not 0 order by value and if value is 0 order by id" to get:

|---id---|-----name-----|--value--|
|    2   |     John     |   800   |
|    1   |     Alex     |   300   |
|    4   |     Carl     |   100   |
|    3   |     Mary     |   0     |
|    5   |     Jesus    |   0     |
|    6   |     Aron     |   0     |

How can I do it?

score 2 · Answer 1 · answered Jul 30 '20 at 18:30

2

If there are no negative values (as shown in your sample data), then adding another sort criteria on id is sufficient:

order by value desc, id

answered Jul 30 '20 at 18:30

GMB

195,563
23
62
110

score 1 · Answer 2 · answered Jul 30 '20 at 18:25

1

you can order by BOOLEAN

SELECT * FROM table
ORDER BY (VALUE<>0)::BOOL DESC, value DESC, ID ASC

this will give what you need

...and playing with all three directions you can get reorder as you want

answered Jul 30 '20 at 18:25

Игорь Тыра

887
3
12

score 0 · Answer 3 · answered Jul 30 '20 at 18:15

0

SELECT * 
FROM table 
WHERE VALUE<>0
ORDER_BY value DESC,ID

It's up to you if add DESC at the end to order the ID descencing.

answered Jul 30 '20 at 18:15

Luuk

9,042
4
20
28

score 0 · Answer 4 · answered Jul 30 '20 at 23:40

Nothing in the question says that values can't be negative, or NULL. To not depend on 0 sorting last:

SELECT *
FROM   tbl
ORDER  BY value = 0, value DESC NULLS LAST, id;

This also sorts possible NULL values last. Those would sort first in DESCENDING order. (You did not specify what to do with those, if any.)

See:

How to order SQL queries with conditionals?

4 Answers4