0

My table has the following structure:

 sensor_id | timestamp  | value
-----------|------------|-------
 1         | 1516905900 | 100.1
 4         | 1516916920 | 90.4
 ...       | ...        | ...

I need to select the columns with the max value for each day for a given sensor_id. I need the timestamp associated with each max value in the results.

I have had some success using MAX() and GROUP BY and DATE_FORMAT, but I cannot get my query to include the timestamp associated with the max value for each group.

This is what I have so far:

select DATE_FORMAT(from_unixtime(timestamp), '%m/%d/%Y') as x, max(value) from sensor_log where sensor_id=6 group by x;

The result only includes the x and max(value) columns. I think it might be possible to use some sort of JOIN to get the rest of the columns, but I haven't been able to figure it out.

Another issue I can foresee is the possibility of a sensor_id having multiple occurrences of the max value on the same day, in this case I would only like to keep the first occurrence of that value for that day.

BBales
  • 6,588
  • 3
  • 16
  • 19

2 Answers2

0

Hmmm. This sounds like a good use of a correlated subquery with a twist:

select date(from_unixtime(sl.timestamp)) as dte, sl.sensor_id, sl.value, sl.timestamp
from sensor_log sl
where sl.timestamp = (select sl2.timestamp
                      from sensor_log sl2
                      where sl2.sensor_id = sl.sensor_id and
                            date(from_unixtime(sl2.timestamp)) = date(from_unixtime(sl.timestamp))
                      order by sl2.value desc, sl.timestamp asc
                      limit 1
                     );
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Starting with MySQL version 8, this can be accomplished with a ranking function dense_rank.

select sensor_id,timestamp,value,DATE_FORMAT(from_unixtime(timestamp),'%m/%d/%Y') as dt
from (select t.*
      ,dense_rank() over(partition by DATE_FORMAT(from_unixtime(timestamp),'%m/%d/%Y') order by value desc) as rnk
      from tbl t
     ) t
where rnk=1
Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58