MySql take maximum value in group by

Question

I have this table:

id|date      |x |y
1 |2017-01-01|1 |0
2 |2017-01-01|23|1
4 |2017-01-02|1 |0

I would select the columns x and date, where the grouped row by date have the highest value of y.

In this case i would:

id|date      |x |
2 |2017-01-01|23|
4 |2017-01-02|1 |

This table is derived from another join:

X table:

date
2017-01-01
2017-01-02
....

Y table:

from      |to        |x |y 
2017-01-01|2017-01-10|1 |0
2017-01-01|2017-01-05|23|1

X join with Y on date between from and to.

How can I do? Thanks in advance.

Possible duplicate of SQL Select only rows with Max Value on a Column — fafl
– fafl, Commented Jan 16, 2017 at 14:51

Tim Biegeleisen · Accepted Answer · 2017-01-16 14:48:11Z

3

In a subquery, find the max(y) for each date, then join to this.

select b.*
from MyTable b
inner join
  (
    select a3.date, max(a3.y) as maxy
    from MyTable a3
    group by a3.date
  ) a
  on a.date = b.date
  and b.y = a.maxy

Demo here:

SQL Fiddle

edited Jan 16, 2017 at 14:48

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

answered Jan 16, 2017 at 14:40

JohnHC

11.2k1 gold badge28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

sirion1987 Over a year ago

Alternatives to join?

Tim Biegeleisen Over a year ago

Hi John, I went ahead and gave you a Fiddle :-)

JohnHC Over a year ago

@TimBiegeleisen Cheers fella

Tim Biegeleisen · Accepted Answer · 2017-01-16 14:49:53Z

2

An alternative to the answer given by @JohnHC would be to use a correlated subquery:

SELECT t1.*
FROM yourTable t1
WHERE t1.y = (SELECT MAX(y) FROM yourTable t2 WHERE t2.date = t1.date)

While I would expect this to run more slowly than the join option, at least for larger datasets, this does have the option of being less verbose.

Demo here:

SQL Fiddle

edited Jan 16, 2017 at 14:49

answered Jan 16, 2017 at 14:43

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Comments

fafl · Accepted Answer · 2017-01-16 14:57:50Z

0

Join the table with itself, where the dates are equal and so that the second table has a bigger x than the first. Then filter so that only results remain, where the second x is NULL, meaning that the first x is the max.

SELECT a.*
FROM myTable a
LEFT OUTER JOIN myTable b
    ON a.date = b.date AND a.x < b.x
WHERE b.x IS NULL;

answered Jan 16, 2017 at 14:57

fafl

7,5074 gold badges32 silver badges54 bronze badges

4 Comments

Strawberry Over a year ago

Note that this solution, while valid, scales poorly and will perform badly on larger data sets - but it's called a 'strawberry' query, so I have a natural fondness for it.

fafl Over a year ago

True, the answer from JohnHC should be the fastest, because the inner query is independent from the outer query

sirion1987 Over a year ago

My table is derived from another join table. Must I use a double-join on the same table?

fafl Over a year ago

Where do the IDs come from? They are not in any table

Collectives™ on Stack Overflow

MySql take maximum value in group by

3 Answers 3

SQL Fiddle

3 Comments

SQL Fiddle

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related