SQL order by column name-CodePudding

I have some data from excel exported to a MySql server. I want to do a query with results sorted with column names as in excel.

I had this query statement:

SELECT CellValue, Col, Row FROM cell WHERE Col != "A" ORDER BY Col ASC;

But the result is order in a way, not what I expected.

AA, AB, AC, B, BA, C, CA, CB, ...

What I want is

B, C, AA, AB, ...

What should I change/add to the query statement to have my wanted results?

CodePudding user response：

Use a two-tier sort, first by the length of col, followed by the value of col:

SELECT CellValue, Col, `Row`
FROM cell
WHERE Col != 'A'
ORDER BY LENGTH(Col), Col;

Note that ROW became a reserved MySQL keyword as of MySQL 8 . You should avoid using this as a name for your columns (or tables).

CodePudding user response：

For MySQL you can sort by length of value first, like this:

with test as (
select 'AA' as id
union select 'AC'
union select 'B'
union select 'BA'
union select 'AAA'
union select 'ABC'
union select 'C'
union select 'CA'
union select 'CB'
union select 'DDDD')
select *
from test
order by char_length(test.id), test.id