Member Avatar for emily-bcot 
-----------------------------------------------------------
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 because: n/a
  • 2 Contributors
  • 1 Reply
  • 188 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by smantscheff
Member Avatar for smantscheff 
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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.