1

I have the following problem: I try to select all the votes a user made and put them out in one column. I use GROUP_CONCAT for this, but somehow it is mixing the values order.

This is the SQL code:

SELECT 
    GROUP_CONCAT(DISTINCT options.option_name SEPARATOR ',') AS selected,
    user_login.firstname, user_login.lastname,
    event.event_title
FROM
    options, user_login, event, votes, questions
WHERE
    event.id = ? AND questions.Event_id = event.id 
    AND votes.user_id = user_login.id AND votes.question_id = questions.id 
    AND FIND_IN_SET(options.id, selected)

GROUP BY user_login.id ORDER BY user_login.class

An example value for votes would be:

id |  event_id  | question_id  |  selected   |  user_id
25       14           42          52,46,41         1
26       14           43             68            1

Options is like:

id |  option_name   | question_id
 40        Project A          42
 41        Project B          42
 46        Project C          42     
 52        Project D          42
 67        Hello              43
 68        Bye                43

Questions is like:

id  |  question_name  | event_id
42     Project Number       14
43     Greeting             14

Event is like:

id  |  title
14     Project Testing

And the output of the given code is:

selected                            |  event_title
Project C, Bye, ProjectD, Project B      Test

How can I keep the original order, so that it outputs me: Project D, Project C, Project B, Bye?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Buffer Overflow
  • 946
  • 1
  • 8
  • 9
  • can you provide some actual data? like the data that would correspond to your desired output? – John Ruddell Jul 26 '14 at 14:56
  • I have added some real data – Buffer Overflow Jul 26 '14 at 15:39
  • [Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 20 years** ago) and its use is discouraged – marc_s Jul 18 '15 at 08:45

1 Answers1

1

would something like this work? basically you say order by the field values and make them look like '52','46',... etc. 

SELECT 
    GROUP_CONCAT(DISTINCT options.option_name 
                 ORDER BY FIELD( options.id, 
                                 concat('"', 
                                        replace(selected, ',', '","'),
                                        '"') 
                               ) 
                 SEPARATOR ','
                ) AS selected,
    user_login.firstname, user_login.lastname,
    event.event_title
FROM options, user_login, event, votes, questions
WHERE event.id = ? AND questions.Event_id = event.id 
  AND votes.user_id = user_login.id AND votes.question_id = questions.id 
  AND FIND_IN_SET(options.id, selected)
GROUP BY user_login.id
ORDER BY user_login.class

EDIT:

my preferred way to do this is to make a variable that has the string.. its easier to read and you can ensure it does the correct order this way..

SET @order_field := (
    SELECT 
        group_concat(
            CONCAT('"', replace(selected, ',', '","'), '"')
        ) 
    FROM votes);

then the query would be a lot easier to read...

SELECT 
    GROUP_CONCAT(DISTINCT options.option_name 
                 ORDER BY FIELD( options.id, @order_field) 
                 SEPARATOR ','
                ) AS selected,
    user_login.firstname, user_login.lastname,
    event.event_title
FROM options, user_login, event, votes, questions
WHERE event.id = ? AND questions.Event_id = event.id 
  AND votes.user_id = user_login.id AND votes.question_id = questions.id 
  AND FIND_IN_SET(options.id, selected)
GROUP BY user_login.id
ORDER BY user_login.class
John Ruddell
  • 25,283
  • 6
  • 57
  • 86