Search code examples
mysqlsortingsql-order-byalphabetical

Use mySQL to order US States alphabetically

Relatively new to mySQL so this is probably an easy one:

I have a table named Zip with three columns: 

Zip varchar(255)
City varchar(255)
State enum('AL','AK','AZ','AR','CA',etc.,etc., 'WY'

When I run the query:

SELECT DISTINCT State FROM Zip ORDER BY State ASC;

I get this result:

+-------+
| State |
+-------+
|       |
| AL    |
| AK    |
| AZ    |
| AR    |
etc.

So it's sorting the list alphabetically by the first letter just fine, but it's NOT sorting alphabetically by the second.

I've searched everywhere (w3schools, dev.mysql.com, stackoverflow, Google) and I couldn't find anything about ORDER BY "the second letter/character" (or, at least, I don't think I found anything; maybe I just didn't understand).

Any ideas on what I can do?

Solution

  • Try converting the enum to a character type:

    SELECT DISTINCT State
FROM Zip
ORDER BY CAST(State AS CHAR)