CODEWITHSUNDEEP
mysqlsqlmariadbsql-order-by
user7187569
user7187569

Reputation:

MySQL Order By Field and Case When Then

I am trying to order by a field, and to order by Case When Than statement. if case is ns, date should ASC, and time should ASC, if case is pp, date should ASC, and time should ASC, if case is done, date should DESC, and time should ASC,

ns is not started, pp is postponed, done is done,

My code:

select * from matches
where tournament_id = 12
order by FIELD(matches.status_key, 'ns', 'done', 'pp'),
CASE WHEN matches.status_key = 'ns' THEN ('matches.date ASC, matches.time ASC') END,
CASE WHEN matches.status_key = 'pp' THEN ('matches.date ASC, matches.time ASC') END,
CASE WHEN matches.status_key = 'done' THEN ('matches.date DESC, matches.time ASC') END;

My guess is that this is a wrong approach, I need some guidance, advice, and help to resolve the problem.

Thank you

Upvotes: 0

Views: 105

Answers (1)

GMB
GMB

Reputation: 222432

I think that should do it:

order by 
    field(status_key, 'ns', 'done', 'pp'),
    case when status_key in ('ns', 'pp') then date,
    case when status_key = 'done'        then date desc,
    time

Upvotes: 1

Related Questions