How to sort within GROUP BY?


Warning: count(): Parameter must be an array or an object that implements Countable in /home/styllloz/public_html/qa-theme/donut-theme/qa-donut-layer.php on line 274
0 like 0 dislike
63 views
Sort within 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

it is necessary to choose the most expensive rooms on the countries and their ID
select id_num, id_country, max(cost) from oper group by id_country
gives just such a thing
id_num id_country max(cost)
1 1 4
5 2 8

ie prices pick correct, but the ID numbers did not match the price tag, should be so
id_num id_country max(cost)
4 1 4
8 2 8
by | 63 views

6 Answers

0 like 0 dislike
>ie prices pick correct, but the ID numbers did not match the price tag, should be so
\r
Actually different and should not be grouped by country, select the maximum price and get "random" number, never indicating that it should match this price. Anyway you need to first obtain the maximum value, and then the number.
by
0 like 0 dislike
I'm not an expert in SQL, but in sqlite, for example, it would be something like:
\r
\r
 SELECT * FROM oper o WHERE cost = ( SELECT MAX(cost) FROM oper p WHERE p.id_country IS o.id_country) ORDER BY id_country;

It is assumed that the cost unique (at least within the country).
by
0 like 0 dislike
by
0 like 0 dislike
solved self joins om
by
0 like 0 dislike
Your request does not match the SQL standard, and that it works, a feature of the MySQL implementation.
Everything that is outside of aggregate functions must be in the GROUP BY.
\r
The problem can be solved using a subquery, self-join or custom aggregate function such as ARGMAX.
by
0 like 0 dislike
by

Related questions

0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
asked Jun 2, 2019 by xonar
0 like 0 dislike
4 answers
110,608 questions
257,186 answers
0 comments
25,316 users