title: MYSQL GROUP_CONCAT with ORDER BY strange sorting results
body: <p>This is my working query, no errors, but the sorting by certain columns is strange.</p>
  
  <pre><code>$query = mysql_query("SELECT ServiceType, Location, Destination, Name, PAX,
                        TransferTime, Ticket SUM(PAX) AS TotalPAX,
                        GROUP_CONCAT( Coupon SEPARATOR '&lt;BR&gt;') AS PasTicket,
                        GROUP_CONCAT( TransferTime SEPARATOR '&lt;BR&gt;') AS PasTransferTime,
                        GROUP_CONCAT( Location SEPARATOR '&lt;BR&gt;') AS PasLocation,
                        GROUP_CONCAT( Destination SEPARATOR '&lt;BR&gt;') AS PasDestination,
                        GROUP_CONCAT( Name SEPARATOR '&lt;BR&gt;') AS PasName,
                        GROUP_CONCAT( PAX SEPARATOR '&lt;BR&gt;') AS PasPAX
                        FROM Reservations WHERE Date = '$DateToday'
                        GROUP BY Ticket
                        ORDER BY ServiceType, TransferTime ASC");
  </code></pre>
  
  <p>This gives me the following results which are the correct data but in wrong order:</p>
  
  <pre>
  REF | SERVICE   | TIME     | PAX | HOTEL      | FROM    | NAME            | TICKET
  ----+-----------+----------+-----+------------+---------+-----------------+----------------
  17  | DEPARTURE | 15:00:00 | 1.0 | IBEROSTAR  | AIRPORT | FRANCIS EUGEN   | TICKET: #110588
  18  | DEPARTURE | 15:20:00 | 2.0 | PARADISUS  | AIRPORT | MARTIN CIBBAR   | TICKET: #110589
  19  | DEPARTURE | 16:40:00 | 2.0 | PARADISUS  | AIRPORT | JOHN CIA        | TICKET: #3376
  20  | DEPARTURE | 16:00:00 | 2.0 | RIU PALACE | AIRPORT | ALAN KRAMER     | TICKET: #110589
  21  | DEPARTURE | 16:10:00 | 2.0 | RIU PALACE | AIRPORT | JOHN WILLS      | TICKET: #110589
  22  | DEPARTURE | 16:20:00 | 2.0 | RIU PALACE | AIRPORT | PETER LONG      | TICKET: #110589
  </pre>
  
  <p>When I <code>GROUP BY Ticket</code> the query works but the Times are out of order, however if I add <code>ORDER BY Time ASC</code> the ORDER BY does not work - <code>GROUP BY Time</code> does work but then I lose the <code>GROUP BY Ticket</code> but  I cannot use <code>GROUP BY Time</code> anyways... Number 18, 20, 21 and 22 should be GROUPED together with Time in ORDER ASC.</p>
  
  <p>The ideal would be something like: <code>WHERE Date = '$DateToday' GROUP BY Ticket, TransferTime (LIKE '%TransferTime%') ORDER BY ServiceType, TransferTime ASC</code>. This is because TransferTimes can differ by some minutes and GROUP BY is too strict.</p>
  
  <p>Below is an example of what it should be, GROUPED BY Ticket Number and sorted by TIME ASC. I have left the REF numbers as they were to make it clearer. Strange thing is, we have this working live but in MSSQL but the system is from 2013 and we need to pass it over to MYSQL and PHP.</p>
  
  <pre>
  REF | SERVICE   | TIME     | PAX | HOTEL      | FROM    | NAME            | TICKET
  ----+-----------+----------+-----+------------+---------+-----------------+----------------
  17  | DEPARTURE | 15:00:00 | 1.0 | IBEROSTAR  | AIRPORT | FRANCIS EUGEN   | TICKET: #110588
  20  | DEPARTURE | 16:00:00 | 2.0 | RIU PALACE | AIRPORT | ALAN KRAMER     | TICKET: #110589
  21  | DEPARTURE | 16:10:00 | 2.0 | RIU PALACE | AIRPORT | JOHN WILLS      | TICKET: #110589
  22  | DEPARTURE | 16:20:00 | 2.0 | RIU PALACE | AIRPORT | PETER LONG      | TICKET: #110589
  18  | DEPARTURE | 16:40:00 | 2.0 | PARADISUS  | AIRPORT | MARTIN CIBBAR   | TICKET: #110589
  19  | DEPARTURE | 16:40:00 | 2.0 | PARADISUS  | AIRPORT | JOHN CIA        | TICKET: #3376
  </pre>
  
  <p>GROUP_CONCAT appears to be working fine. Any ideas whats going on?</p>
  
  <p>Screen dump image (names and flights changed). The data is complete and correct, but the sorting order of the ticket number is off. I realize this is because I am using GROUP BY Time which is too strict because Time can vary somewhat.</p>
  
  <p>PS: The GROUP_BY SEPARATOR is a line break BR which for some reason is not being published.
  <a href="https://i.stack.imgur.com/8bGGn.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/8bGGn.jpg" alt="Screen Dump"></a></p>