0
-----------------------------------------------------------
chan1   |chan2  |chan3  | captureTime       |id
-----------------------------------------------------------
20  |21 |25 | 2011-10-11 00:00:00   |1
-----------------------------------------------------------
21  |18 |19 | 2011-10-11 00:01:00   |1
-----------------------------------------------------------
                ....
                ....
                ....
-----------------------------------------------------------
20  |25 |21 | 2011-10-11 00:15:00   |1
-----------------------------------------------------------
21  |18 |14 | 2011-10-11 00:16:00   |1
                ....
                ....
                ....
-----------------------------------------------------------
15  |26 |23 | 2011-10-11 23:58:00   |1
-----------------------------------------------------------
17  |14 |32 | 2011-10-11 23:59:00   |1

The table store chan1,chan2 and chan3 value every one minute. I want to get the maximum & minimum values for every 10 minutes and also have the corresponding the captureTime for min/max values.

Based on those requirments, How to write the sql query? Thanks!

Edited by emily-bcot: n/a

2
Contributors
1
Reply
2
Views
6 Years
Discussion Span
Last Post by smantscheff
0
select greatest(t1.chan1,t1.chan2,t1.chan3), captureTime
from myTable t1
where greatest(t1.chan1,t1.chan2,t1.chan3) = (
  select max(greatest(t2.chan1, t2.chan2, t2.chan3))
  from myTable t2
  group by substr(t2.captureTime, 1, 15)
)
and substr(t1.captureTime, 1, 15 ) = substr( t2.captureTime, 1, 15)

Lots of query optimization needed, I presume.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.