CODEWITHSUNDEEP
mysqlsql
mana
mana

Reputation: 1239

Order by CASE with duplicate values

I have a database table "tbl_matches"

  NAME     START_DATE         STATUS
  John     2018-08-1 08:30    Prematch
  Paul     2018-08-1 05:30    Prematch
  Sam      2018-08-1 05:30    Prematch
  Mel      2018-08-1 05:30    Live

Here's my query

SELECT * FROM tbl_matches
ORDER BY CASE WHEN STATUS = 'Live' THEN true ELSE NULL DESC

What I want is to move "Mel" record above Paul, just between Paul and John, because Mel status has Live and also it has the same date and time with Paul and Sam.

The logic should be: If the status is "Live" then move the record above other records has the same "Date and Time".

Upvotes: 0

Views: 280

Answers (2)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

just use case when for status column and put START_DATE also in order by clause

SELECT * FROM tbl_matches    
order by START_DATE,case when status ='Live' then 0 else 1 end

Upvotes: 2

akshay
akshay

Reputation: 777

Try:

SELECT * FROM tbl_matches
ORDER BY START_DATE, CASE WHEN STATUS ='Live' THEN '0' ELSE '1' END;

CASE will assign the value 0 where Status is Live otherwise it will assign 1. Then ORDER BY will sort it in Ascending order by default

Upvotes: 2

Related Questions