菜单

mysql 5.7 group by order by

2017年05月1日 - mysql
It seems that all ORDER BY will be ignored if they stand in sub-query before GROUP BY in mysql 5.7.

I have the same issue in my query. I found work-around. You can add "ORDER BY" by key column. If id is primary key in your table, you can add

GROUP BY `id`
before

ORDER BY FIELD(lang, 'EN', 'JP')
Query:

`SELECT *
FROM
(
  SELECT  `id`, `category`, `name`, `number`, `lang`
  FROM `test`
  WHERE `category` = 'Cat1'
  GROUP BY `id`
  ORDER BY FIELD(lang, 'EN', 'JP')
) as table
GROUP BY number
ORDER BY number DESC`

发表评论

电子邮件地址不会被公开。 必填项已用*标注