mysql custom order by with mixed data types
mysql, sql-order-by
Solution
`FIELD()` returns `0` when the search string is not found. Therefore:
ORDER BY FIELD(size, 'XXS', 'XS', 'S', 'M', 'L', 'XL', 'XXL', 'XXXL'), size
Problem
In the following mysql query I'm using a custom order by statement so I can display various sizes in a specific order instead of alphabetical: ``` select distinct size from product p left join productsizes ps on p.productcode = ps.size_prodcode order by field(size, 'XXS', 'XS', 'S', 'M', 'L', 'XL', 'XXL', 'XXXL') ``` In cases where some products also have numeric sizes, how do I write the order by so that it places the numeric sizes in an ascending order along with the custom order? An example of the desired output: 30, 32, 34, S, M, L or S, M, L, 30, 32, 34