MySQL - Order a Group By Statement With a Column Not in List

Question

So here is my SQL

SET group_concat_max_len = 18446744073709551615;

SELECT
    `event`.title AS 'Event Title',
    eventitem.startdate AS 'Start Date',
    `get_unique_items` (
        CONVERT (
            group_concat(eventstage.title SEPARATOR ',') USING utf8
        )
    ) AS 'Stage',
    `get_unique_items` (
        CONVERT (
            group_concat(artists.artistname SEPARATOR ',') USING utf8
        )
    ) AS 'Artist Name'
FROM
    eventstage
INNER JOIN `event` ON eventstage.eventid = `event`.eventid
INNER JOIN eventitem ON eventitem.eventstageid = eventstage.eventstageid
INNER JOIN artists ON eventitem.artistid = artists.artistid
GROUP BY
    eventstage.eventid,
    eventitem.eventstageid,
    eventitem.startdate

The get_unique_items does as the name suggests. It removes any duplicates.

Now each record has an eventitem has a ranking field. Each artist has a ranking, with 0 denoting the highest and higher numbers denoting a lower ranking.

How can manipulate the SQL so in the results, under the 'Artist Name' column, I get a list of artists in ranking order?

I don't believe it to be the same as MySQL: Sort GROUP_CONCAT values since I'm not using the function on the ranking field Thanks

Where would I put the ORDER BY since I'm not outputting the ranking at all or using GROUP_CONCAT on the table. Sorry @Strawberry what do you mean what does it do??? — pee2pee, Aug 16 '15 at 11:49
You're using no aggregating functions, so why do you have a GROUP BY clause in your query? — Strawberry, Aug 16 '15 at 12:45

Gordon Linoff · Accepted Answer · 2015-08-17T02:01:28.513

It is unclear why you have a separate function to remove duplicates, when GROUP_CONCAT() supports the DISTINCT keyword. I think the following does what you want:

SELECT e.title AS `Event Title`,
       i.startdate AS `Start Date`,
       group_concat(DISTINCT s.title ORDER BY i.ranking SEPARATOR ',') as Stage,
       group_concat(DISTINCT a.artistname ORDER BY i.ranking SEPARATOR ',') as  `Artist Name`
FROM eventstage s INNER JOIN
     `event` e
     ON s.eventid = e.eventid INNER JOIN
     eventitem i
     ON i.eventstageid = s.eventstageid INNER JOIN
     artists a
     ON i.artistid = a.artistid
GROUP BY s.eventid, i.eventstageid, i.startdate;

Three notes:

Table aliases make the query easier to write and to read. Use them.
Don't use single quotes for column aliases. Single quotes should only be used for string and date constants.
I am guessing that ranking is in eventitems, but it might be in another table.

Thanks will give it a go - I will update question with my DB schema shortly — pee2pee, Aug 16 '15 at 19:30

MySQL - Order a Group By Statement With a Column Not in List

1 Answers1