Conditional Group By sql query

Question

I would like to sort database query by:

number value DESCENDING considering String property from another field

Something like:

ORDER BY money DESC,
currency = 'EUR'

Example:

money    |     currency
-----------------------
200      |     EUR
300      |     USD
500      |     USD
100      |     EUR
400      |     EUR

I would like to sort money in descending fashion only when currency is equal to EUR. I don't want other currency to be considered in sorting such as:

money    |     currency
-----------------------
400      |     EUR
200      |     EUR
100      |     EUR
300      |     USD
500      |     USD

I don't need the sorting after EUR. Could be totally random

Possible duplicate of [SQL multiple column ordering](https://stackoverflow.com/questions/2051162/sql-multiple-column-ordering) — kiner_shah, Jun 25 '18 at 12:00

score 2 · Accepted Answer · answered Jun 25 '18 at 12:00

2

You can use multiple keys in the order by:

order by (currency = 'EUR') desc,
         money desc

This orders the remaining currencies in descending order. That is a by-product. If you specifically want them to be random:

order by (currency = 'EUR') desc,
         (case when currency = 'EUR' then money end) desc,
         rand()

But that seems like overkill.

answered Jun 25 '18 at 12:00

Gordon Linoff

1,242,037
58
646
786

ill test it and get back to you with answer – krzakov Jun 25 '18 at 12:02
works as a charm. I need to translate it to queryDSL somehow, seems to be tricky – krzakov Jun 25 '18 at 12:17

score 1 · Answer 2 · answered Jun 25 '18 at 12:09

1

If you really want an order condition only on 'EUR' value you can do something like this :

 ORDER BY (currency = 'EUR') * money DESC

answered Jun 25 '18 at 12:09

A. Colonna

852
7
10

Conditional Group By sql query

2 Answers2