CODEWITHSUNDEEP
mysqlsql-order-by
user2474683
user2474683

Reputation: 29

MySQL ORDER BY Customized

I have these rows y DB and I would like order by but in the below order with caracters and number. The colum Score is a varchar. WINNER and LOSER are in Score colum also.

Score

WINNER
100+
100
90
80+
80
50
LOSER

Upvotes: 0

Views: 77

Answers (1)

Ed Gibbs
Ed Gibbs

Reputation: 26333

This approach converts the score value to a number when ordering. I tried it with your data, then with your data plus additional values, and it worked both times:

SELECT score
FROM myTable
ORDER BY
  CASE
    WHEN score = 'WINNER' THEN 100000
    WHEN score = 'LOSER' THEN -100000
    WHEN score LIKE '%+' THEN score * 100 + 99
    ELSE score * 100
 END DESC

The conversion is as follows:

  • WINNER = 100,000
  • LOSER = -100,000
  • number+ = number * 100 + 99
  • number = number

Upvotes: 1

Related Questions