开发者

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

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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜