MySQL GROUP_CONCAT with ORDER BY

Question

I have a SELECT with a GROUP_CONCAT column as follows:

SELECT
    m.*,
    GROUP_CONCAT(s.id ORDER BY s.id ASC SEPARATOR ', ') AS schedule_ids

FROM months m

LEFT JOIN schedule s
    ON (MONTH(s.start_date) = m.id) OR (MONTH(s.end_date) = m.id)

GROUP BY m.id

This column currently returns a comma separated string of 2 IDs, eg: 1, 2 or 11, 12.

I want to add an IF condition to the ORDER BY, whereby in the specific instance where the IDs returned are 1, 12, the ORDER BY should be DESC, i.e. it should return 12, 1, otherwise keep it as ASC.

How can I do this?

Gordon Linoff · Accepted Answer · 2021-02-04 23:27:06Z

1

If that is your very specific condition, then I would suggest a CASE expression:

(CASE WHEN GROUP_CONCAT(s.id ORDER BY s.id ASC SEPARATOR ', ') = '1, 12'
      THEN '12, 1'
      ELSE GROUP_CONCAT(s.id ORDER BY s.id ASC SEPARATOR ', ') 
 END)

answered Feb 4, 2021 at 23:27

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL GROUP_CONCAT with ORDER BY

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related