MySQL ORDER BY results not alphabetical
I have been trying to find a solution to this problem but have not had any luck. I know there are several similar questions here but they are all more complicated scenarios that do not really apply. Okay, the issue:
I have a very simple table with a list of transaction payees and their associated category. The structure is id - int
, name- varchar
, and cat - varchar
I am trying to do a simple query to present the payee names in alphabetical order, added to a select box using a while
loop. Regardless of whether or not I use ASC or DESC, they are displayed in no apparent order There is no white space in the beginning or end of the entries.
Here is the query - SELECT name FROM payees ORDER BY name
I have the same result when attempting to sort them alphabetically in phpMyAdmin. id
is an auto increment int field and the primary key.
Thanks!
UPDATE
Here is example output - SELECT name FROM payees ORDER BY name ASC
- Sheetz
- Walgreen开发者_运维问答s
- Arby's
- Dollar General
- Exxon
- iTunes
- Red Box
- Starbucks
- Walmart
Collation is utf8_unicode_ci
Solved
Kudos to @JohnFlatness for solving the puzzle. This turned out to actually be a flaw in code - this project has a mobile and regular version. There was a bug in the scripting of the mobile version that was adding a space to the beginning of the name
column entries. The entries I spot checked for this were fine, missed the ones that had white space.
Thanks for the help - solved via comments, a first for me lol
Those fields probably do not contain exactly the data you think they do. Try dumping an output of SELECT HEX(name) FROM payees
to show exactly what is in each field. Here is what the output should be for reference:
417262792773 Arby's
446F6C6C61722047656E6572616C Dollar General
4578786F6E Exxon
6954756E6573 iTunes
52656420426F78 Red Box
53686565747A Sheetz
537461726275636B73 Starbucks
57616C677265656E73 Walgreens
57616C6D617274 Walmart
Demo: http://sqlize.com/904pho3tUG
SELECT name FROM payees ORDER BY trim(name)
Also verify your table collation ends in *_ci
精彩评论