Dean Christian Armada Dean Christian Armada - 3 months ago 9
MySQL Question

MYSQL Ordering an array

This is my result

+----------------------------------------------------------------------------------------------------------------------------------------------+
| SUBSTRING(COLUMN_TYPE,5) |
+----------------------------------------------------------------------------------------------------------------------------------------------+
| ('Sedan','Hatch','Convertable','Van','Coupe','Light Truck','People Mover','SUV','Ute','Wagon','Cab Chassis','Sample Body','Body 4','BOdy 5') |
+----------------------------------------------------------------------------------------------------------------------------------------------+


This is my query

SELECT SUBSTRING(COLUMN_TYPE,5) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='Ad_tbl' AND COLUMN_NAME='body_type'


I want the array to be ordered the other way.. So the Sedan would be in the last of the array instead of first.. Already Tried ORDER BY either ASC or DESC but no luck

Answer

You're querying the list of items in an ENUM definition, and you want to change the order? You can't do that without using ALTER TABLE to change your ENUM.

The order of items in an ENUM is related to the physical storage of the values. 'Sedan' is 1, 'Hatch' is 2, 'Convertable' (sic) is 3, etc. Changing the order of these strings requires changing the enumeration values.

Of course, you could change the order of displaying the strings in your application code. But this means parsing out the items from that list, splitting on comma, removing quotes and parens, etc.

But doing similar text-parsing in pure SQL will be an exercise in frustration, or at least, it'll be a huge waste of time.

This awkwardness of fetching the items in an ENUM definition is one of the reasons MySQL's ENUM data type is evil.

If you want to control the sort order without redefining the table, you'll be better off using a lookup table instead of an ENUM.

Comments