There is a field 'noticeBy' enum('email','mobile','all','auto','nothing') NOT NULL DEFAULT 'auto'. As it known ordering by ENUM field performs relative to its index. However, how it possible make order by its values?
Add a comment
|
5 Answers
As documented under Sorting:
ENUMvalues are sorted based on their index numbers, which depend on the order in which the enumeration members were listed in the column specification. For example,'b'sorts before'a'forENUM('b', 'a'). The empty string sorts before nonempty strings, andNULLvalues sort before all other enumeration values.To prevent unexpected results when using the
ORDER BYclause on anENUMcolumn, use one of these techniques:
Specify the
ENUMlist in alphabetic order.Make sure that the column is sorted lexically rather than by index number by coding
ORDER BY CAST(col AS CHAR)orORDER BY CONCAT(col).
Per the second bullet, you can therefore sort on the column after it has been cast to a string:
ORDER BY CAST(noticeBy AS CHAR)
2 Comments
jtubre
Very nice, thank you! Odd that this best (highest voted) and accepted answer is not at the top? If it's for lack of comments, hoping this one will help.
eggyal
@jtubre: There are three sort buttons between the question and answers: "Active", "Oldest" and "Votes". If you sort by votes, this answer should be top? But it won't be top under the other sort orders (it used to be that accepted answers were pinned to the top regardless, but StackOverflow changed that a few months ago).
This also works:
ORDER BY FIELD(noticeBy, 'all','auto','email','mobile','nothing')
(I don't believe that there is a setting to achieve this, you have to provide the sort-values.)
2 Comments
abdulmanov.ilmir
This way of sorting ENUM can be very useful some cases! It's really works, I have tried it:)
Andrew
perfect exactly what i was looking for
You can define your order however you wish:
ORDER BY CASE noticeBy
WHEN 'email' THEN 1
WHEN 'mobile' THEN 2
WHEN 'all' THEN 3
WHEN 'auto' THEN 4
ELSE 5
END
This will return the rows in the following order: email, mobile, all, auto, nothing.
Comments
The best option to me:
ORDER BY FIELD(status, 'publish','not-publish','expirated','deleted'), creation DESC
Status is the field in my BBDD, and values in '' are the values that has in enum options.
I hope that help u too! :)
Comments
In my case, I had to sort enum results by the "ENUM" field and also make sure the values are in DESCENDING order. My enum had the following values: 'Open','Closed'
So when I used ORDER BY CAST(status AS CHAR), the results were in this order:
Closed
Open
Open
But I wanted the Open status tickets to be shown first and then the Closed tickets. So I used the following:
ORDER BY CAST(status AS CHAR) DESC
This gave me the order that I was looking for i.e.
Open
Open
Closed
Summary:
Just using ORDER BY CAST on an enum did not seem to help. To sort the results in a specific order, mentioning ASC or DESC as well, did the trick.
Comments
