CODEWITHSUNDEEP
mysqlsql
AndrewS
AndrewS

Reputation: 1565

MySQL Order by CASE

I cant make it work for me. 

ORDER BY cl.name ASC');

I must change to:

ORDER BY CASE cl.name WHEN "Nameone" THEN 0 WHEN "Nametwo" THEN 1 ELSE 2 END ASC

I get the result

1. Nameone
2. Nametwo

(and the rest but its not ordered by name from A-Z but it displays i think by id)

So how to make the rest result to be ordered by cl.name ASC?

Upvotes: 0

Views: 4664

Answers (3)

Trung Duong
Trung Duong

Reputation: 3475

You could change your ORDER BY clause like this. 

 ORDER BY CASE cl.name WHEN 'Nameone' THEN '0' WHEN 'Nametwo' THEN '1' ELSE CONCAT('2', COALESCE (cl.name,'')) END ASC

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

In MySQL, you can use the field() function, if you like:

order by field(c1.name, 'Nametwo', 'NameOne') desc,
         c1.name

The arguments are in reverse order, because field() defaults to 0 when the string is not found.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521053

Just add a second level of sorting to your ORDER BY clause:

ORDER BY
    CASE WHEN cl.name = 'Nameone' THEN 0
         WHEN cl.name = 'Nametwo' THEN 1
         ELSE 2 END,
    cl.name            -- add this sort condition

Note that adding cl.name as a second sorting condition will have no effect for records where the name is Nameone or Nametwo, because the name is always the same for those records.

Upvotes: 5

Related Questions