MySQL ORDER BY returns things in (apparently) random order?

Question

The following query:

SELECT DISTINCT ClassName FROM SiteTree ORDER BY ClassName 

returns things without a visible order!

I get the same result, can I specify column / table names or use DISTINCT or not, or add ASC or DESC .

I suggested that indexes might be broken or something like this, so I tried to reset and recreate. Also tried REPAIR TABLE and CHECK TABLE .

To sort the table, set latin1_swedish_ci. All text columns are configured to use UTF-8, and sorting is set to utf8_general_ci

What could be the reason for this?

Edit:

Sample pastie data.

Results directly from the SQL query executed in the MySQL client (two different client applications).

Answer 1

Eureka!

Although it is true that using a function will return the correct order, for example:

  SELECT DISTINCT ClassName FROM SiteTree ORDER BY REPLACE(ClassName,'','') 

It turns out I was looking at the ENUM column (I forgot, I thought it was plain text), and so MySQL sorted according to the order of the elements in ENUM.

Thanks for the helpful tips.

Here's a reasonable solution, given the situation.

 SELECT DISTINCT ClassName FROM SiteTree ORDER BY CAST(ClassName AS CHAR) 

Answer 2

How did your data load? I have seen several cases where loading from some external source placed a space or other similar character in the first position of the line - with the result on which the set of returned results was actually sorted, but not in the way you would expect.

It can be incredibly difficult to detect, and if I get abnormal results like the one you see in the first things that I usually do, select the field in question, the concatenation of '>' and '<.

Answer 3

There is no reason why this should work if you look to see if the mixture gets rid of the camel shell:

  SELECT DISTINCT LOWER(LTRIM(ClassName)) AS classname FROM SiteTree ORDER BY classname 

I really liked the Cruachan idea, I updated my idea to cover it. Remove leading spaces.

Answer 4

Perhaps, or most likely, the table will be sorted before DISTINCT ing. Try using an aggregate function or GROUP BY

Answer 5

If you are viewing the results on a web page, you can simply see the cached result set from an earlier query.