[RESOLVED] Complex SQL - ordering by an expression involving a GROUP_CONCAT value

[mitya]

Hi all

Not sure if there's a way round this. Here's my query:

SELECT
survey_name,
GROUP_CONCAT(survey_questions.id) as questions
FROM
surveys
LEFT JOIN
survey_questions
ON
surveys.id = survey_questions.survey_id
GROUP BY
survey_name
ORDER BY
LENGTH(questions) - LENGTH(REPLACE(questions, ',', ''))

This attempts to fetch surveys ordered by the number of questions they contain. The group_concat 'questions' returns a comma-separated list of question IDs. The order by expression is a way of counting the number of questions in the string by comparing the length before and after removing the comma separators.

The query returns the error 'Reference 'questions' not supported (reference to group function)'.

p.s. if I replace my order by expression with a simple reference to 'questions', it works fine. So it seems I can reference the group_concat column in my order by, just not use it with any functions or as part of an expression. Can't imagine why this is.

Anyone see a way round this?

Thanks in advance

[ssystems]

SELECT
survey_name,
count(survey_questions.id) as question_count
,GROUP_CONCAT(survey_questions.id) as questions
FROM
surveys
LEFT JOIN
survey_questions
ON
surveys.id = survey_questions.survey_id
GROUP BY survey_id
ORDER BY question_count

[mitya]

Thanks - looks like that works.

[ssystems]

No problem. Make sure to mark as resolved.

[mitya]

Sure - how do I do that? Can't seem to see a link, nor in the CP.

[ssystems]

Upper right should be under thread tools.

[mitya]

Ah yes, ta.