We recently updated some code in which we use a “case” statement within an order clause, as follows:
order by
case d.wo
when ‘e’ then a.location
when ‘a’ then c.w
when ‘r’ then RAND()
end asc";
where a.location is a tinyint.
Sorting directly by a.location works fine, values are sorted correctly, i.e. 1…9,10,11… etc.
Using the case statement as above, treats “a.location” as a character and causes the sort order to change, i.e. 1,10,11,2,3…9 etc.
I tried forcing a cast in the case statement on a.location but that did not work. Any suggestions? is this a bug?