MYSQL Ordering an array

Question

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

ORDER BY `COLUMN_TYPE` DESC. Using DESC instead of ASC will sort it the other way. — UeliDeSchwert, Aug 15 '16 at 06:17
I updated the query,, actually ORDER BY doesn't take effect whether I set it to ASC or DESC — Dean Christian Armada, Aug 15 '16 at 06:20
Take a look on [**ORDER BY FIELD**] [http://dba.stackexchange.com/questions/109120/how-does-order-by-field-in-mysql-work-internally] — Michail M., Aug 15 '16 at 06:22
The result you provided seems not the result of your query .. you have substring of 5 and the result seem a group_concat .. ???? — ScaisEdge, Aug 15 '16 at 06:52
That is the result of the query.. But I want that array result to be in a reverse order — Dean Christian Armada, Aug 15 '16 at 06:55
since on of your tags is php, you could also reverse the order in php, when you put the whole thing into an array. (array_reverse) — Jakumi, Aug 15 '16 at 07:16
Then you probably shouldn't tag your question php... anyway. Order by could be called on ordinal_position? — Jakumi, Aug 15 '16 at 07:31
oh wait, misread your query. Do I understand correctly, that you're querying the column information for an enum field? — Jakumi, Aug 15 '16 at 07:38
I fear there's no clean solution in mysql only, that I can think of. Apart from changing the database schema to make the enum field a foreign key of another table, that just contains the values of the enum. In php it would be quite easy: http://stackoverflow.com/questions/2350052/how-can-i-get-enum-possible-values-in-a-mysql-database — Jakumi, Aug 15 '16 at 07:46

score 2 · Accepted Answer · answered Aug 15 '16 at 08:06

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.

Ok noted on this one – Dean Christian Armada Aug 15 '16 at 08:17 — Dean Christian Armada, Aug 15 '16 at 08:17

MYSQL Ordering an array

1 Answers1