CODEWITHSUNDEEP
mysqlmysql-workbench
Chris
Chris

Reputation: 433

Order by ordinal_position not producing expected output

UPDATE

I have a table with the index in order:

fsym_id, currency, x, y, z,

and the columns in order

currency, fsym_id, x, y, z

I would like to extract the names of my index in the right order.

I tried to do the following:

set session group_concat_max_len = 1000000;
select GROUP_CONCAT(CONCAT("'",COLUMN_NAME,"'"))
from INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table'
AND TABLE_SCHEMA = 'schema'
ORDER BY ORDINAL_POSITION

But this gives me the ordering of the columns. How can I modify this to return the ordering of the indexes?

Upvotes: 0

Views: 1341

Answers (1)

Barmar
Barmar

Reputation: 782499

If you want to order the values in a GROUP_CONCAT() you have to put the ORDER BY option in that function. Your ORDER BY clause is for ordering the rows; since you're only returning one row, it has no effect.

Also, it's not necessary to use CONCAT() inside GROUP_CONCAT(). If you give multiple values, they're automatically concatenated.

select GROUP_CONCAT("'",COLUMN_NAME,"'" ORDER BY ORDINAL_POSITION)
from INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table'
AND TABLE_SCHEMA = 'schema'

Upvotes: 2

Related Questions