CODEWITHSUNDEEP
sqlpostgresqlsql-order-by
Torcat
Torcat

Reputation: 51

How to order SQL queries with conditionals?

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?

Upvotes: 0

Views: 57

Answers (4)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656391

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:

Upvotes: 0

GMB
GMB

Reputation: 222432

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

Upvotes: 2

Игорь Тыра
Игорь Тыра

Reputation: 955

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

Upvotes: 1

Luuk
Luuk

Reputation: 14899

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.

Upvotes: 0

Related Questions