CODEWITHSUNDEEP
mysql
K_H
K_H

Reputation: 29

How I can get the first row of each hour?

MySQL []> show columns from reading;

| Field      | Type       | Null | Key | Default | Extra          |
| cont       | int(4)     | NO   | PRI | NULL    | auto_increment |
| id         | int(4)     | YES  | MUL | NULL    |                |
| sensor_num | int(4)     | YES  |     | NULL    |                |
| value      | float(5,2) | YES  |     | NULL    |                |
| time       | datetime   | YES  |     | NULL    |                |

select * from reading;

  18570 |    1 |          1 | 23.93 | 2017-08-14 12:45:01 |
| 18571 |    1 |          1 | 23.93 | 2017-08-14 12:46:01 |
| 18572 |    1 |          1 | 23.93 | 2017-08-14 12:47:01 |
| 18573 |    1 |          1 | 23.93 | 2017-08-14 12:48:01 |
| 18574 |    1 |          1 | 23.93 | 2017-08-14 12:49:02 |
| 18575 |    1 |          1 | 23.93 | 2017-08-14 12:50:02 |
| 18576 |    1 |          1 | 23.93 | 2017-08-14 12:51:02 |
| 18577 |    1 |          1 | 23.93 | 2017-08-14 12:52:02 |
| 18578 |    1 |          1 | 23.93 | 2017-08-14 12:53:02 |
| 18579 |    1 |          1 | 23.93 | 2017-08-14 12:54:02 |
| 18580 |    1 |          1 | 23.93 | 2017-08-14 12:55:02 |
| 18581 |    1 |          1 | 23.93 | 2017-08-14 12:56:02 |
| 18582 |    1 |          1 | 23.93 | 2017-08-14 12:57:02 |

| 18689 |    1 |          1 | 25.88 | 2017-08-14 14:54:56 |
| 18690 |    1 |          1 | 25.88 | 2017-08-14 14:55:56 |
| 18691 |    1 |          1 | 25.88 | 2017-08-14 14:56:56 |
| 18692 |    1 |          1 | 25.88 | 2017-08-14 14:57:56 |
| 18693 |    1 |          1 | 25.88 | 2017-08-14 14:58:56 |
| 18694 |    1 |          1 | 25.88 | 2017-08-14 14:59:56 |
| 18695 |    1 |          1 | 25.88 | 2017-08-14 15:00:56 |
| 18696 |    1 |          1 | 25.88 | 2017-08-14 15:01:56 |
| 18697 |    1 |          1 | 25.88 | 2017-08-14 15:02:56 |
| 18698 |    1 |          1 | 25.88 | 2017-08-14 15:03:56 |
| 18699 |    1 |          1 | 25.88 | 2017-08-14 15:04:56 |
| 18700 |    1 |          1 | 25.88 | 2017-08-14 15:05:56 |

Upvotes: 0

Views: 122

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133370

If you want the first rows for each hour, you can select the rows that match the min(time) group by hour:

  select * 
  from reading a
  inner join (
    select date(time) t_date, min(time) t_time
    from reading 
    group by date(time), hour(time)
  ) t on  t.t_time = a.time

Upvotes: 1

Related Questions