CODEWITHSUNDEEP
mysql
Greg
Greg

Reputation: 1813

Mysql - order by custom?

I have a mysql query that is returning the following data:

select Desc,Value from table;

Desc        Value
 a           4
 r           3
 e           4
 j           7
 w           6
 p           6

I want to order by Descr but in a specific order (This specific is required for json)

Desc        Value
 r           3  
 a           4
 e           4
 j           7
 p           6
 w           6

Is this possible?

Upvotes: 1

Views: 253

Answers (3)

zerkms
zerkms

Reputation: 255155

ORDER BY FIELD(`desc`, 'r', 'a', 'e', 'j', 'p', 'w')

Note: if desc value doesn't match the given values, it will come first, as FIELD() would return 0

Upvotes: 2

Mureinik
Mureinik

Reputation: 312344

You could order by a case expression that specifies the ordering you want:

SELECT   `desc`, `value`
FROM     my_table
ORDER BY CASE `desc` WHEN 'r' THEN 1 
                     WHEN 'a' THEN 2
                     WHEN 'e' THEN 3
                     WHEN 'j' THEN 4
                     WHEN 'p' THEN 5
                     WHEN 'w' THEN 6
                     ELSE 999 --for completeness sake
          END CASE ASC

Upvotes: 0

h2ooooooo
h2ooooooo

Reputation: 39550

It's not pretty, but you can order by (condition) DESC. DESC because if the condition is true it's more than if the condition is false:

SELECT
    Desc,
    Value
FROM
    Foo
ORDER BY
    (Desc = 'r') DESC,
    (Desc = 'a') DESC,
    (Desc = 'e') DESC,
    (Desc = 'j') DESC,
    (Desc = 'p') DESC,
    (Desc = 'w') DESC

Upvotes: 0

Related Questions