«

»

Dec 14

Get Record with Highest Amount in Given Hour

We can achieve this approach using join table it self.

create table t1(id int, date datetime, amount int)

insert into data values
(1,’2014-10-11 15:15:00′,150),
(2,’2014-10-11 15:20:00′,50),
(3,’2014-10-11 18:30:00′,100),
(4,’2014-10-11 18:40:00′,10),
(5,’2014-10-11 21:10:00′,40),
(6,’2014-10-11 21:20:00′,80);

Table Structure with data

ID         Date                                     Amount

1         2014-10-11 15:15:00             150
2         2014-10-11 15:20:00               50
3         2014-10-11 18:30:00             100
4         2014-10-11 18:40:00                10
5         2014-10-11 21:10:00                40
6         2014-10-11 21:20:00                80

Now Sql query to show highest amount in given hour

select * from data d
join (select max(amount) maxamount, hour(date) datehour, date(date) date
from data group by hour(date), date(date)
) d2 on d.amount = d2.maxamount
and hour(d.date) = d2.datehour
and date(d.date) = d2.date

Result

ID      Date                                            Amount

1        2014-10-11 15:15:00                    150
3        2014-10-11 18:30:00                    100
6        2014-10-11 21:20:00                      80

 

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>