CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
sirion1987
sirion1987

Reputation: 111

MySql take maximum value in group by

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.

Upvotes: 2

Views: 3911

Answers (3)

fafl
fafl

Reputation: 7385

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;

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520948

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

Upvotes: 2

JohnHC
JohnHC

Reputation: 11195

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

Upvotes: 3

Related Questions