group by together with order by
The multilanguage of the site is made by displaying more popular values from the mysql database, which have collected a greater number of votes.
The table consists of:
|id|text_id|module|lang| text |voices|uid|
------------------------------------------
|1 | 1 |anketa| ru |Ашибка| 1 | 1 |
------------------------------------------
|2 | 1 |anketa| ru |Ошибка| 2 | 2 |
I got the request:
SELECT *
FROM `langs`
WHERE `module`='anketa'
AND `lang`='ru'
GROUP BY `text_id`
ORDER BY `voices` DESC
But it outputs the value that is the first in the list with the minimum number of voices, and should output only with the largest number of voices.
Help me make the correct query.
3 answers
Solution to the question:
SELECT
`l`.`text_id`, `l`.`text`, `l`.`voices`
FROM `langs` `l`
INNER JOIN (
SELECT
`text_id`, MAX(`voices`) AS `MaxVoice`
FROM `langs`
WHERE `module`='anketa' AND
`lang`='ru'
GROUP BY `text_id`
) `lm`
ON `l`.`text_id` = `lm`.`text_id` AND
`l`.`voices` = `lm`.`MaxVoice` AND
`l`.`module` = 'anketa' AND
`l`.`lang` = 'ru'
GROUP BY `l`.`text_id`
ORDER BY `l`.`text_id` ASC
SELECT MAX(`voices`) AS `voices`, `id`
FROM `langs`
WHERE `module`='anketa'
AND `lang`='ru'
GROUP BY `text_id`
According to the SQL standard, in a query with grouping, in the field enumeration part (what comes after SELECT), you can specify ONLY those fields that are grouped by, or that are used with group functions (sum, max, group_concat,...). MySQL allows queries that do not meet this rule. In this case, for fields without group functions and not specified in the GROUP BY part, an arbitrary string from the group is selected. For examples, see Grouping in MySQL