CODEWITHSUNDEEP
mysqlsqlsql-order-by
StackOverflowNewbie
StackOverflowNewbie

Reputation: 40653

MySQL - ordering by something not ASC or DESC

Say I have a table of flowers with associated colors. Is it possible to get a list of flowers and order them by, say, Yellow first, then Blue, then Red. Basically, I want to specify a list of values and order the result by those values. Possible?

Upvotes: 6

Views: 2420

Answers (3)

kapa
kapa

Reputation: 78751

My choice would be using the FIELD(str, str1, str2, ...) function.

Returns the index (position) of str in the str1, str2, str3, ... list. Returns 0 if str is not found.

So you could use the return value to order your list of results:

ORDER BY FIELD (color, 'Yellow', 'Blue', 'Red')

If you want to reverse the order, just add DESC.

Upvotes: 2

Deepu S Nath
Deepu S Nath

Reputation: 1184

You can add a field in colors table for sort_order and and provide values according to the sort requirement. In the query you can use ORDER BY sort_order ASC

I am expecting that you want to order it according to a dynamic requirement not explicitly for these three colors mentioned. This will allow you complete flexibility to manage the sort order.

Upvotes: 2

OMG Ponies
OMG Ponies

Reputation: 332791

You could use FIND_IN_SET:

ORDER BY FIND_IN_SET(column, 'Yellow,Blue,Red')

..or the ANSI CASE statement:

ORDER BY CASE column
           WHEN 'Yellow' THEN 1
           WHEN 'Blue' THEN 2
           WHEN 'Red' THEN 3
         END

Upvotes: 10

Related Questions