MySQL: ORDER BY not working as expected. Orders some rows correctly

Question

I don't have very much experience with MySQL so I am not sure what information I should include when asking this question. If I am missing something relevant leave a comment. I will promptly add it.

I have this query:

SELECT 
        Log_Analysis_RecordsToSesions_dalhousie.sessionID, 
        (TIMEDIFF(
                MAX(Log_Analysis_Records_dalhousie.date), 
                MIN(Log_Analysis_Records_dalhousie.date) 
        )) as NewSessionLength
FROM 
        Log_Analysis_RecordsToSesions_dalhousie, 
        Log_Analysis_Records_dalhousie
WHERE
        Log_Analysis_RecordsToSesions_dalhousie.recordID=Log_Analysis_Records_dalhousie.recordID
GROUP BY
        sessionID;

Which returns this:

+-----------+---------------+
| sessionID | SessionLength |
+-----------+---------------+
|         1 | 00:20:31      | 
|         2 | 00:19:54      | 
|         3 | 00:04:01      | 
|         4 | 00:14:02      | 
|         5 | 00:02:16      | 
|         6 | 00:16:25      | 
|         7 | 00:00:00      | 
|         8 | 00:00:00      | 
|         9 | 00:00:00      | 
|        10 | 00:26:35      | 
|        11 | 00:11:28      | 
|        12 | 00:00:00      | 
|        13 | 00:00:00      | 
|        14 | 00:11:03      | 
  ...
|      7978 | 00:00:03      | 
|      7979 | 00:00:15      | 
|      7980 | 00:00:00      | 
|      7981 | 00:00:00      | 
+-----------+---------------+
7981 rows in set (0.92 sec)

When I append

ORDER BY
        NewSessionLength;

To the query it mostly sorts it but does not get it 100% correct. Most of the big values get put at the end of the table and most of the small values get put at the beginning. I have no idea what is causing this.

For example:

+-----------+------------------+
| sessionID | NewSessionLength |
+-----------+------------------+
|      4285 | 00:00:00         | 
|      1565 | 00:00:00         | 
|      7604 | 00:00:02         | 
|      4317 | 00:00:02         | 
|      1597 | 00:00:02         | 
|      7636 | 00:00:01         | 
|      4349 | 00:18:17         | // <-------------- Out of order
|      1629 | 00:00:09         | 
  ...
|      2829 | 04:28:01         | 
|       377 | 04:25:23         | // <-------------- Out of the order
|      5631 | 05:37:25         | 
|        18 | 05:05:31         | 
|      1545 | 07:01:31         | 
|      7536 | 07:09:59         | 
|      5237 | 10:07:29         | 
|       250 | 90:42:30         | 
+-----------+------------------+
7981 rows in set (0.94 sec)

Answer 1

In MySQL version 5.0.50 there was a bug (#32202) where an ORDER BY would not work correctly when used with a GROUP BY. This has been fixed in more recent versions, but you may want to try the following to see if you have a "faulty" MySQL version:

CREATE TABLE a (id int auto_increment primary key, sorter char(5));
INSERT INTO a VALUES (1, 'z'), (2, 'x'), (3, 'a'), (4, 'z');
SELECT * FROM a GROUP BY id ORDER BY sorter;

If the result set you get is not sorted by the sorter field, then you may want to consider upgrading your database server.

If an upgrade is not possible, there is one easy workaround for the above:

SELECT * FROM ( SELECT * FROM a GROUP BY id ) b ORDER BY sorter;

... which in your case should be the following:

SELECT * FROM 
(
    SELECT 
            Log_Analysis_RecordsToSesions_dalhousie.sessionID, 
            (TIMEDIFF(
                    MAX(Log_Analysis_Records_dalhousie.date), 
                    MIN(Log_Analysis_Records_dalhousie.date) 
            )) as NewSessionLength
    FROM 
            Log_Analysis_RecordsToSesions_dalhousie, 
            Log_Analysis_Records_dalhousie
    WHERE
            Log_Analysis_RecordsToSesions_dalhousie.recordID = 
            Log_Analysis_Records_dalhousie.recordID
    GROUP BY
            sessionID
) dt
ORDER BY NewSessionLength;