Order By before a Group By

Question

I need to pull some data on a couple thousand records, but I'm having trouble sorting the results.

SELECT IdCode, GROUP_CONCAT(' ',CONCAT(MONTHNAME(ei.StartDatetime),' ',YEAR(ei.StartDatetime))) 
FROM exclusion_import_data eid JOIN exclusion_import ei ON ei.ImportId = eid.ImportId 
WHERE ( IdCode IN ( '84277', '253533' ))  
GROUP BY IdCode 
ORDER BY RecordId ASC;

Basically I'm trying to see which months specific records appeared in our system.

253533 May 2013, November 2013, December 2013, January 2014, February 2014, March 2014, April 2014, May 2014, June 2014, August 2014, October 2013, September 2013, June 2013, July 2013, May 2013, August 2013 84277 September 2013, April 2014, May 2013, May 2014, June 2014, May 2013, March 2014, June 2013, February 2014, January 2014, July 2013, December 2013, November 2013, August 2013, October 2013, August 2014

The above query returns the correct months, separated by the idCodes, but the months are all out of order.

How can I sort the records before grouping them?

Mgmt summary: you can `group_concat(myfield ORDER BY myfield ASC)`, so in your case add `ORDER BY ei.StartDateTime` to the `group_concat`. — Niels Keurentjes, Sep 11 '14 at 14:44

score 1 · Answer 1 · answered Sep 11 '14 at 14:46

Use the order by option in the group_concat():

SELECT IdCode,
       GROUP_CONCAT(' ', CONCAT(MONTHNAME(ei.StartDatetime),' ',YEAR(ei.StartDatetime))
                    ORDER BY ei.StartDatetime)
FROM exclusion_import_data eid JOIN
     exclusion_import ei
     ON ei.ImportId = eid.ImportId 
WHERE ( IdCode IN ( '84277', '253533' ))
GROUP BY IdCode 
ORDER BY RecordId ASC;

spencer7593 · Accepted Answer · 2014-09-11T15:03:43.610

You can specify an ordering within the GROUP_CONCAT function, for example:

GROUP_CONCAT(DATE_FORMAT(ei.StartDatetime,'%M %Y') ORDER BY ei.StartDatetime) 
                                                   ^^^^^^^^^^^^^^^^^^^^^^^^^

(My preference would be to get the month and year with a single function call, and one reference to the column, rather than three function calls and two references to the same column. I've demonstrated that above as well.)

Ref: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

Order By before a Group By

2 Answers2