If then else conditions in order by clause-CodePudding

I have a table with the following fields:

id
discount_amount
discount_expires_at

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

CodePudding user response：

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;

CodePudding user response：

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.