Home mysql group by together with order by

group by together with order by




The site is multilingual by displaying more popular values ​​from the mysql database that have collected a larger number of votes.

The table consists of:

| id | text_id | module | lang | text | voices | uid |
| 1 | 1 | anketa | ru | Ashibka | 1 | 1 |
| 2 | 1 | anketa | ru | Error | 2 | 2 |

I got a request:

FROM `langs`
WHERE `module` = 'anketa'
 AND `lang` = 'ru'
GROUP BY `text_id`
ORDER BY `voices` DESC

But it outputs the first value, which in the list with the minimum number of voices , but should only output the value with the largest number of voices.

Help me write the correct request.

Answer 1, authority 100%

Solution to the issue:

  `l`.`text_id`,` l`.`text`, `l`.`voices`
FROM `langs`` l`
    `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

Answer 2

SELECT MAX (`voices`) AS` voices`, `id`
FROM `langs`
WHERE `module` = 'anketa'
 AND `lang` = 'ru'
GROUP BY `text_id`

Answer 3

According to the SQL standard, in a grouped query in the part of the enumeration of fields (what comes after SELECT), you can specify ONLY those fields by which the grouping is performed, or which are used with group functions (sum, max, group_concat, …) … MySQL allows queries that do not match this rule. In this case, for fields without group functions and not specified in the GROUP BY part, an arbitrary row from the group is selected. For examples see MySQL grouping

Programmers, Start Your Engines!

Why spend time searching for the correct question and then entering your answer when you can find it in a second? That's what CompuTicket is all about! Here you'll find thousands of questions and answers from hundreds of computer languages.

Recent questions