How to sort inside GROUP BY?

Question

MySQL

Sorting inside GROUP BY
CREATE TABLE `oper` (
`id_num` int(10) unsigned NOT NULL auto_increment,
`id_country` int(10) unsigned NOT NULL,
`cost` decimal(4,2) unsigned NOT NULL default '0.00',
PRIMARY KEY (`id_num`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
id_num id_country cost
1 1 1
2 1 2
3 1 3
4 1 4
5 2 5
6 2 6
7 2 7
8 2 8
select the most expensive numbers by country and their id
select id_num, id_country, max(cost) from oper group by id_country
gives something like this
id_num id_country max(cost)
1 1 4
5 2 8
i.e. selects the correct prices, but the number id does not correspond to the price tag, it should be like this
id_num id_country max(cost)
4 1 4
8 2 8

Answer 1

no, not with a subquery...

Sure, not a problem:

SELECT o.*
    FROM oper AS o
        JOIN oper AS o2
    ON o.id_country = o2.id_country
        GROUP BY o.id_country, o.id_num
        HAVING o.cost = MAX(o2.cost)

Answer 2

> that is, it selects the correct prices, but the number id does not correspond to the price tag in any way, it should be like this.
Actually, it should not be different - group by country, select the maximum price and display a “random” number, nowhere indicating that it should correspond to this price. One way or another, you must first get the maximum value, and then the number for it.

Answer 3

I'm not an expert in SQL, but in sqlite for example it would be something like this:

  SELECT *
    FROM opera o
   WHERE cost = (
         SELECT MAX(cost)
           FROM opera p
          WHERE p.id_country IS o.id_country)
ORDER BY id_country;

It is supposed to be costunique (at least within the country).

Answer 4

you can use a temporary table.
https://demiart.ru/forum/journal.php?user=1&comm=306504

Answer 5

habrahabr.ru/qa/437/
isn't it?

Answer 6

solved by self join

Answer 7

Your query does not conform to the SQL standard at all, and the fact that it works is a feature of the MySQL implementation.
Anything outside of the aggregate functions must be in the GROUP BY.
The task can be solved through a subquery, self-join, or a custom aggregate function like ARGMAX.