CODEWITHSUNDEEP
mysqlsql
Marcipanas
Marcipanas

Reputation: 161

SQL using LIKE in ORDER BY CASE

I need to sort like this:
First results are all who start with CHA then MAS, DIA, PLA, GOL, SIL, BRO

Like this:
1. CHALLENGER I
2. CHALLENGER I
3. MASTER II
4. MASTER III
5. PLATINUM II
6. PLATINUM IV
7. PLATINUM V
and ect.

Most important thing is to sort only by the name and not the roman number at the end however if that is easy would appreciate if someone would show a way.

As of now this is my SQL:

  SELECT rank FROM table 
  ORDER BY
   CASE rank
      WHEN rank LIKE 'CHA%' THEN 1
      WHEN rank LIKE 'MAS%' THEN 2
      WHEN rank LIKE 'DIA%' THEN 3
      WHEN rank LIKE 'PLA%' THEN 4
      WHEN rank LIKE 'GOL%' THEN 5
      WHEN rank LIKE 'SIL%' THEN 6
      WHEN rank LIKE 'BRO%' THEN 7
      WHEN rank LIKE 'Unranked' THEN 8
      WHEN rank LIKE 'No Data' THEN 9
      ELSE 10
   END;

However this does not work. I am working with MySQL database. Any help appreciated.

Upvotes: 0

Views: 126

Answers (1)

Fuzzy
Fuzzy

Reputation: 3810

What you have is this:

SELECT [rank] FROM table 
  ORDER BY
   CASE  [rank]
      WHEN [rank] LIKE 'CHA%' THEN 1
      WHEN [rank] LIKE 'MAS%' THEN 2
      WHEN [rank] LIKE 'DIA%' THEN 3
      WHEN [rank] LIKE 'PLA%' THEN 4
      WHEN [rank] LIKE 'GOL%' THEN 5
      WHEN [rank] LIKE 'SIL%' THEN 6
      WHEN [rank] LIKE 'BRO%' THEN 7
      WHEN [rank] LIKE 'Unranked' THEN 8
      WHEN [rank] LIKE 'No Data' THEN 9
      ELSE 10
   END;

What you want to do is this:

    SELECT [rank] FROM table 
      ORDER BY
       CASE  
          WHEN [rank] LIKE 'CHA%' THEN 1
          WHEN [rank] LIKE 'MAS%' THEN 2
          WHEN [rank] LIKE 'DIA%' THEN 3
          WHEN [rank] LIKE 'PLA%' THEN 4
          WHEN [rank] LIKE 'GOL%' THEN 5
          WHEN [rank] LIKE 'SIL%' THEN 6
          WHEN [rank] LIKE 'BRO%' THEN 7
          WHEN [rank] LIKE 'Unranked' THEN 8
          WHEN [rank] LIKE 'No Data' THEN 9
          ELSE 10
       END;

Upvotes: 1

Related Questions