SQL datetime calculation

Applications Programming
1

Suppose I have a mysql table consisting of measurements taken during irregular intervals as follows:

CREATE TABLE data (datetime DATETIME, value INTEGER);

mysql> SELECT datetime, value FROM data;
+---------------------+---------+
| datetime            | value   |
+---------------------+---------+
| 2010-09-12 13:25:38 |   41538 | 
| 2010-09-12 13:25:55 |   41543 | 
| 2010-09-12 23:01:06 |   43364 | 
| 2010-09-12 23:01:46 |   43365 | 
| 2010-09-12 23:02:40 |   43366 | 
| 2010-09-12 23:02:48 |   43367 | 
| 2010-09-16 19:13:50 |   75538 | 
| 2010-09-16 19:19:27 |   75539 | 
+---------------------+---------+

What I would like to do is select the values which are the maximum in their minute, so the output should be as follows:

41543
43365
43367
75538
75539

So 41543 is selected because its datetime is 2010-09-12 13:25:55, which is after 2010-09-12 13:25:38. How do I achieve this?

2 
select t1.b 
  from (
  select       
    max(a) m  
  from       
    data
  group by       
    date_format(a, '%Y%m%d%k%i')
    ) t2
join data t1
on  t1.a = t2.m
order by t1.b;

For example:

mysql> select * from t;
+---------------------+-------+
| a                   | b     |
+---------------------+-------+
| 2010-09-12 13:25:38 | 41538 |
| 2010-09-12 13:25:55 | 41543 |
| 2010-09-12 23:01:06 | 43364 |
| 2010-09-12 23:01:46 | 43365 |
| 2010-09-12 23:02:40 | 43366 |
| 2010-09-12 23:02:48 | 43367 |
| 2010-09-16 19:13:50 | 75538 |
| 2010-09-16 19:19:27 | 75539 |
+---------------------+-------+
8 rows in set (0.01 sec)

mysql> select t1.b
    ->   from (
    ->   select
    ->     max(a) m
    ->   from
    ->     t
    ->   group by
    ->     date_format(a, '%Y%m%d%k%i')
    -> ) t2
    -> join t t1
    -> on  t1.a = t2.m
    -> order by t1.b;
+-------+
| b     |
+-------+
| 41543 |
| 43365 |
| 43367 |
| 75538 |
| 75539 |
+-------+
5 rows in set (0.01 sec)
2 Likes
3

Works great. Thank you very much.