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

kiner_shah
– kiner_shah

2018-06-25 12:00:59 +00:00
Commented Jun 25, 2018 at 12:00 — kiner_shah
– kiner_shah, Commented Jun 25, 2018 at 12:00

Gordon Linoff · Accepted Answer · 2018-06-25 12:00:17Z

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, 2018 at 12:00

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

krzakov Over a year ago

ill test it and get back to you with answer

krzakov Over a year ago

works as a charm. I need to translate it to queryDSL somehow, seems to be tricky

A. Colonna · Accepted Answer · 2018-06-25 12:09:25Z

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, 2018 at 12:09

A. Colonna

8827 silver badges10 bronze badges

Collectives™ on Stack Overflow

Conditional Group By sql query

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related