CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
Petr Hurtak
Petr Hurtak

Reputation: 2377

SQL get record with highest amount in given hour

I am trying to get a record with highest amount in given hour.

Data in DB:

id | date                | amount 
–––––––––––––––––––––––––––––––––
1  | 2014-07-11 18:10:00 | 10
2  | 2014-07-11 18:20:00 | 20
3  | 2014-07-11 18:30:00 | 100
4  | 2014-07-11 18:40:00 | 10
5  | 2014-07-11 19:10:00 | 50
6  | 2014-07-11 19:20:00 | 60

Desired outcome:

id | date                | amount 
---------------------------------
3  | 2014-07-11 18:30:00 | 100
6  | 2014-07-11 19:20:00 | 60

Upvotes: 1

Views: 145

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270181

You can also approach this with the not exists approach:

select d.*
from data d
where not exists (select 1
                  from data d2
                  where date(d2.date) = date(d.date) and
                        hour(d2.date) = hour(d.date) and
                        d2.amount > d.amount
                 );

This is just an alternative solution. Sometimes not exists is faster than the group by with join, but probably not in this case.

Upvotes: 2

Satwik Nadkarny
Satwik Nadkarny

Reputation: 5135

This should suffice:

SELECT      Id, 
            Dating,
            Amount
FROM        Tab
WHERE       Amount IN (SELECT       MAX(Amount) AS 'Maximum Amount'
                       FROM         Tab
                       GROUP BY     HOUR(Dating), DATE(Dating))

You can see that here-> SQL Fiddle Demo

Hope this helps!!!

Upvotes: 1

John Ruddell
John Ruddell

Reputation: 25862

something like this? -- assuming table name is transactions

SELECT
    *
FROM
(
    SELECT 
        id, date, amount
    FROM transactions
    ORDER BY amount DESC
) AS t
GROUP BY HOUR(date) , DATE(date);

WORKING FIDDLE

Upvotes: 2

sgeddes
sgeddes

Reputation: 62841

If I'm understanding your question correctly, you can join the table back to itself using the max aggregate, grouping by the hour and date:

select d.*
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

Upvotes: 4

Related Questions