0

I have a table that contains daily entries for 2 different categories. Example:

Date         |  CategoryID  |  Value
-----------------------------------
2016/02/01        1              1
2016/02/02        1              0
2016/02/03        1              2
2016/02/01        2              1
2016/02/02        2              1
2016/02/03        2              0
.
.
.
2017/02/01        1              0
2017/02/02        1              3
2017/02/03        1              1
2017/02/01        2              2
2017/02/02        2              1
2017/02/03        2              0

What I am trying to do is go through the table and match the day for each year (example 02/01) for each category then get the maximum value over the years.

So the result should be something like for category 1:

02/01        1              1
02/02        1              3
02/03        1              2

02/01        2              2
02/02        2              1
02/03        2              0

I'm just not 100% sure the correct way to do this efficiently especially if this table gets quite large. There will be additional categories added in the future.

Klaus
  • 1

1 Answers1

0

Try below using date_format(), group by and max aggregation

select date_format(`date`,'%m-%d') as d, categoryid, max(value) as value
from tablename
group by date_format(`date`,'%m-%d'),categoryid
Fahmi
  • 37,315
  • 5
  • 22
  • 31