What is the correct sequence of ORDER BY and GROUP BY

Question

I am beginner in mysql Can anybody help me with the error on line number 5:

select a.*, count(b.user_id) as attempts from survey a 
left join survey_attempt b on a.survey_id = b.attempt_surveyid 
where a.survey_status != 0 
order by survey_order asc 
GROUP by a.survey_id

specify table name in this line `order by a.survey_order asc`. — Hardik Solanki, Mar 12 '16 at 10:27
I was trying to have group by and order by, I got answer from below help, thanks guys — Devanshu Saini, Mar 12 '16 at 12:14

score 4 · Answer 1 · answered Mar 12 '16 at 10:27

4

Your ORDER BY should be after GROUP BY.

answered Mar 12 '16 at 10:27

Tibin Paul

806
9
23

score 2 · Answer 2 · answered Mar 12 '16 at 10:26

Use Group By clause before Order by Clause.

As below :

select a.*, count(b.user_id) as attempts from survey a 
left join survey_attempt b on a.survey_id = b.attempt_surveyid 
where a.survey_status != 0 
GROUP by a.survey_id
order by survey_order asc

score 1 · Answer 3 · edited May 23 '17 at 12:32

You are not allowed to ORDER before you GROUP, so your query should look like this:

select a.*, count(b.user_id) as attempts 
from survey a 
   left join survey_attempt b on a.survey_id = b.attempt_surveyid 
where a.survey_status != 0 
group by a.survey_id
order by survey_order asc

More details can be found here, where a similar question was asked and received more elaborated answers.

As a side note, try avoiding * and put required column instead.

What is the correct sequence of ORDER BY and GROUP BY

3 Answers3