CODEWITHSUNDEEP
sqlpostgresqlsql-order-by
Diyan Slavov
Diyan Slavov

Reputation: 433

If then else conditions in order by clause

I have a table with the following fields:

How can I query all of the records and order them by discount_amount, but only if the discount_expires_at is greater than the current time?

All rows must be included anyway, but they should appear first only if the discount has not expired as of now. If it has expired, then they must be sorted by id, descending

Upvotes: 0

Views: 146

Answers (2)

Salman Arshad
Salman Arshad

Reputation: 272066

You can simply use case expressions:

select *
from t
order by
    case when discount_expires_at > current_timestamp then 1 else 2 end,
    case when discount_expires_at > current_timestamp then discount_amount end desc,
    case when discount_expires_at > current_timestamp then null else id end desc

The expression discount_expires_at > current_timestamp condition will either be true or false/unknown; select the desired column based on these two possible outcomes.

Upvotes: 1

Stefanov.sm
Stefanov.sm

Reputation: 13029

Order descending by a composite value, (1, discount_amount) if discount is still in effect or (0, id) if not. The first constant - 1 or 0 - makes expired discounts come after the active ones.

select * from the_table
order by 
 case
   when discount_expires_at > current_timestamp then (1, discount_amount)
   else (0, id)
 end desc;

Upvotes: 2

Related Questions