CODEWITHSUNDEEP
mysqlsql
Lukas
Lukas

Reputation: 510

get one/fist row per hour for the actual day

i need to get one/the first row per hour for the actual day. I dont get it work. This is my table:

ID  Date        Time        Data    SID
1   2017-02-08  12:20:00    x   1
2   2017-02-09  08:20:00    x   1
3   2017-02-09  08:22:00    x   1
4   2017-02-09  08:30:00    x   1
5   2017-02-09  09:03:00    x   1
6   2017-02-09  10:20:00    x   1

the result should look like this:

ID  Date        Time        Data    SID
2   2017-02-09  08:20:00    x   1
5   2017-02-09  09:03:00    x   1
6   2017-02-09  10:20:00    x   1

any ideas?

Upvotes: 0

Views: 34

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

This is filtering, so you can use where. One method is a correlated subquery:

select t.*
from t
where t.time = (select min(t2.time)
                from t t2
                where t2.date = t.date and
                      hour(t2.time) = hour(t.time)
               );

Upvotes: 3

Related Questions