CODEWITHSUNDEEP
mysqlsql
user1300214
user1300214

Reputation:

MySQL CASE not working for ORDERY BY where two columns are used

I want to use the following SQL:

SELECT * FROM Data WHERE tid=1 ORDER BY CASE WHEN round=0 THEN date ELSE round, date END ASC

but it doesn't work due to the round, date bit. Works fine if I make that bit round e.g. but not with both.

If round=0 then I want to be able to sort ascending on round first then date. Is that possible?

What I'm trying to achieve is a sorted array of rows which is sorted on round first then date second. But, if round is zero then I just want that row to appear inbetween the dates where that record would fit even though it would have a zero round value.

Upvotes: 0

Views: 37

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

A case expression returns only one value. If you want rows with round = 0 first, then you can do:

ORDER BY (CASE WHEN round = 0 THEN 1 ELSE 2 END),
         round, date

This can be shortened to:

ORDER BY (round = 0) DESC, round, date

However, I'm not 100% sure this what you really want.

If round only takes on two values then your question would make more sense. In this case, you really want to sort by date first:

ORDER BY date, round;

However, that is speculation on what the data values look like.

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

Could be using a couple of case when one for each column 

  SELECT * 
  FROM Data 
  WHERE tid=1 
  ORDER BY ( CASE WHEN round= 0  THEN date ELSE round  END) ASC , 
           ( CASE WHEN round= 0  THEN NULL  ELSE date  END) ASC

Upvotes: 0

Related Questions