CODEWITHSUNDEEP
mysql
DinhNgocHien
DinhNgocHien

Reputation: 717

Getting max value in a specific peroid of time in MySQL

I have a table like this(tblFuel):

time                    fuel
2014-11-04 17:11:08     231
2014-11-04 17:34:16     254
2014-11-04 18:03:48     241
2014-11-04 18:41:34     137
2014-11-04 18:43:42     111

Now I expect to show the biggest value of fuel during each 1 hour. For example: max from 17:00:00 to 17:59:59 and so on. And follow the previous requirement, the expected result should:

time                    fuel
2014-11-04 17:34:16     254
2014-11-04 18:03:48     241

So what should I do to achieve this result?

Upvotes: 2

Views: 60

Answers (3)

Tom
Tom

Reputation: 6663

Here's one way to do it. It uses the DATE_FORMAT function to group by date and hour.

SELECT ft.time, ft.fuel

FROM   fuel_table ft

       JOIN
       (SELECT DATE_FORMAT(time, '%Y%m%d %H') date_and_hour, MAX(fuel) max_fuel
        FROM   fuel_table
        GROUP BY date_and_hour) AS max_fuel
        ON DATE_FORMAT(ft.time, '%Y%m%d %H') = max_fuel.date_and_hour 
        AND ft.fuel = max_fuel

Upvotes: 0

Saravana Kumar
Saravana Kumar

Reputation: 3729

This query will be helpful.

 DECLARE @tblFuel TABLE
(
    Val INTEGER,
    Time DATETIME
)

INSERT INTO @tblFuel  

SELECT '231', '2014-11-04 17:11:08' union All
SELECT '254', '2014-11-04 17:34:16' union All
SELECT '241', '2014-11-04 18:03:48' union All
SELECT '137', '2014-11-04 18:41:34' union All
SELECT '111', '2014-11-04 18:43:42'  

SELECT A.Val, A.Time FROM @tblFuel AS A 
Inner join 
(SELECT MAX(Val) AS VAL,  
        CONVERT(VARCHAR(20), Time, 110) +' ' + CAST(DATEPART(hour, Time) as varchar(2)) AS Time 
 FROM @tblFuel GROUP BY CONVERT(VARCHAR(20), Time, 110) +' ' + CAST(DATEPART(hour, Time) as varchar(2))) AS B
ON A.Val = B.val AND  CONVERT(VARCHAR(20), A.Time, 110) +' ' + CAST(DATEPART(hour, A.Time) as varchar(2)) = B.Time

Upvotes: 0

Alex Woolford
Alex Woolford

Reputation: 4563

create table tblFuel (time timestamp, fuel int);

insert into tblFuel values ('2014-11-04 17:11:08', 231);
insert into tblFuel values ('2014-11-04 17:34:16', 254);
insert into tblFuel values ('2014-11-04 18:03:48', 241);
insert into tblFuel values ('2014-11-04 18:41:34', 137);
insert into tblFuel values ('2014-11-04 18:43:42', 111);

select
   *
from tblFuel
where concat(date(time), hour(time), fuel) in
    (select
       concat(date(time), hour(time), max(fuel))
     from tblFuel
     group by
        date(time),
        hour(time))

Returns:

time                 fuel
2014-11-04 17:34:16  254
2014-11-04 18:03:48  241

Upvotes: 2

Related Questions