david0215 david0215 - 2 months ago 10
MySQL Question

Mysql: Get the max value with corrsponding time and group by day

My table is:

timelog | roomtemp
2014-07-14 01:16:04 | 25.0 |
2014-07-14 02:14:05 | 26.0 |
2014-07-14 03:13:05 | 25.0 |
2014-07-14 05:13:11 | 29.0 |
2014-07-14 06:15:05 | 25.0 |
2014-07-14 07:17:13 | 30.0 |
.
.
2014-07-15 01:13:05 | 25.0 |
2014-07-15 01:16:04 | 31.0 |
2014-07-15 02:14:05 | 25.0 |
2014-07-15 03:13:05 | 28.0 |
2014-07-15 05:13:05 | 25.0 |


I want to get the max(roomtemp) with corresponding timelog for each day
such as:

2014-07-14 07:17:13 | 30.0 |
2014-07-15 01:16:04 | 31.0 |


I tired following for 2014-07-14, but fail~:

$sql="SELECT timelog, MAX(roomtemp) FROM $tablename WHERE timelog BETWEEN '2014-07-14 00:00:00' AND '2014-07-14 23:59:59' ";<p>
$result=mysqli_query($db, $sql) or die("ERR201: Error");


It shows error message

I can get the maximum roomtemp if I delete timelog which after SELECT

$sql="SELECT MAX(roomtemp) FROM $tablename WHERE timelog BETWEEN '2014-07-14 00:00:00' AND '2014-07-14 23:59:59' ";<p>
$result=mysqli_query($db, $sql) or die("ERR201: Error");


I also tried group by DAY(timelog), but timelog value is not correct...

Answer

Query to get day and max temperature on that day:

select date(timelog) as day,max(roomtemp) from table_name group by day;

Output:

enter image description here

Query to get time and max temerature on a day:

SELECT timelog AS curr_day,MAX(roomtemp) FROM (SELECT * FROM day_temperature ORDER BY roomtemp DESC) c GROUP BY DATE(curr_day);

Output:

enter image description here

SQL dump:

/*
SQLyog Ultimate v10.00 Beta1
MySQL - 5.5.24-log 
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;

create table `day_temperature` (
    `timelog` datetime ,
    `roomtemp` float 
); 
insert into `day_temperature` (`timelog`, `roomtemp`) values('2014-07-19 10:27:20','25');
insert into `day_temperature` (`timelog`, `roomtemp`) values('2014-07-19 10:30:21','27');
insert into `day_temperature` (`timelog`, `roomtemp`) values('2014-07-20 11:10:20','28');
insert into `day_temperature` (`timelog`, `roomtemp`) values('2014-07-20 12:00:00','23');