Select additional column value in group by

Question

Please see the mysql table data below. What I would like to do is to have the MIN(price) per date, but select the matching some_id as well.

SELECT date, MIN(price), some_id
FROM table
GROUP BY date

That doesn't work since mysql 5.7 introduced full group by mode as it's default.

Any ideas? Note that it's a massive table with millions of rows.

date        price   some_id
2016-04-09  10.66   3535475
2016-04-09  11.53   2429075
2016-04-09  11.53   2119575
2016-04-09  12.53   1431700
2016-04-09  12.55   2119375
2016-04-10  8.58    885050
2016-04-10  13.20   3535475
2016-04-10  14.27   2429075
2016-04-10  14.27   2119575
2016-04-10  14.70   13591
2016-04-11  11.98   885050
2016-04-11  12.44   2119375
2016-04-11  13.00   3535475
2016-04-11  13.79   2119575
2016-04-11  14.27   2429075
2016-04-11  14.91   13409
2016-04-11  15.38   2152375
2016-04-11  15.64   1431700

Is that table data, or the current result, or the wanted result? We need them all! — jarlh, Sep 21 '16 at 13:40
That's table data. The wanted result would be a single row per `date`, with the `MIN(price)` + the matching `some_id` — Tjeerd Kramer, Sep 26 '16 at 08:40

score 2 · Accepted Answer · answered Sep 21 '16 at 13:40

2

Try using your query as a derived table and JOIN back to the original table to get filed some_id as well:

SELECT t1.date, t1.price, t1.some_id
FROM table t1
JOIN (
    SELECT date, MIN(price) as min_price
    FROM table 
    GROUP BY date
) t2 ON t1.date = t2.date AND t1.price = t2.min_price

answered Sep 21 '16 at 13:40

Giorgos Betsos

71,379
9
63
98

Thanks! This (almost) did what I wanted it to do. Just had to add a `SELECT DISTINCT` to it. – Tjeerd Kramer Sep 26 '16 at 08:46

score 0 · Answer 2 · edited May 23 '17 at 12:15

For future reference, full_group_by mode being enforced by default is something that will help you write more SQL compliant database queries. It is something that is also enforced by default on PostgreSQL, for example.

You can do what you want in two ways. The first is just by using WHERE to limit your data set before aggregating it:

SELECT date, MIN(price) FROM table WHERE some_id = [YOUR ID HERE] GROUP BY date

The second is using HAVING to select your some_id after you've done the aggregation:

SELECT some_id, date, MIN(price) FROM table GROUP BY some_id, date HAVING some_id = [YOUR ID HERE]

Here's a good answer describing the difference between WHERE and HAVING.

score 0 · Answer 3 · answered Sep 21 '16 at 13:41

0

What if you get the groupping first and then perform a join like

SELECT t.some_id, xxx.`date`, xxx.minprice
from table t join (
SELECT date, MIN(price) as minprice
FROM table 
GROUP BY date) xxx on t.date = xxx.date;

answered Sep 21 '16 at 13:41

Rahul

76,197
13
71
125

score 0 · Answer 4 · answered Sep 21 '16 at 13:44

0

Source MySQL 5.7 Reference Manual - 13.20.3 MySQL Handling of GROUP BY

You can achieve the same effect without disabling ONLY_FULL_GROUP_BY by using ANY_VALUE() to refer to the nonaggregated column.

Thus :

SELECT date, MIN(price), ANY_VALUE(some_id)
FROM table
GROUP BY date

answered Sep 21 '16 at 13:44

Thomas G

9,886
7
28
41

The result returned by `ANY_VALUE` is non-deterministic. Hence, it cannot be used to obtain the required `some_id` value. – Giorgos Betsos Sep 21 '16 at 13:48
@GiorgosBetsos correct me if I am wrong, but it's original `GROUP BY` query was also non-deterministic for the `some_id` field. My understanding of OP question is that he doesnt want (or cant) change the `ONLY_FULL_GROUP_BY` mode on the server, but still want to get its non-deterministic results, which the `ANY_VALUE()` basically allows. – Thomas G Sep 21 '16 at 13:55
Yes, this is correct. `ANY_VALUE` essentially masks `ONLY_FULL_GROUP_BY` mode. But the OP seems to want the *matching* `some_id` value, i.e. the value of the record having the minimum `price` value. – Giorgos Betsos Sep 21 '16 at 13:59

score 0 · Answer 5 · edited Sep 21 '16 at 15:42

0

This should work:

SELECT date, MIN(price), some_id
FROM table
GROUP BY date
HAVING some_id='search id'

HAVING clause works on groups.

edited Sep 21 '16 at 15:42

gr1zzly be4r

2,072
1
18
33

answered Sep 21 '16 at 13:49

dkum

105
8

Select additional column value in group by

5 Answers5