Re: a difficult SQL request

That is exactly what I am looking for, and that is really a genious idea. But I need to read more about lag/over to understand it, I do not even know such thing exists.

Thank you very much, Buck.

"Buck Turgidson" <jc_va_at_hotmail.com> wrote in message news:bO86a.58921$P1.3752150_at_news1.east.cox.net...
> Thought about your question a little more. There is probably a slicker
way
> to do this, but this will answer the question "which row has 7 previous
> consecutive rows with the same volume.
>
> I.e. at 12:14, there existed 7 previous records of volume 3. The same for
> minute 27, 28, and 29.
>
>
>
>
>
> SQL> select to_char(c_time,'hh:mi'),volume from data_collector
> 2 order by c_time;
>
> TO_CH VOLUME
> ----- ----------
> 12:01 1
> 12:02 2
> 12:03 3
> 12:04 3
> 12:05 3
> 12:06 4
> 12:07 3
> 12:08 3
> 12:09 3
> 12:10 3
> 12:11 3
> 12:12 3
> 12:13 3
> 12:14 3
> 12:15 6
> 12:16 8
> 12:17 2
> 12:18 2
> 12:19 2
> 12:20 9
> 12:21 9
> 12:22 9
> 12:23 9
> 12:24 9
> 12:25 9
> 12:26 9
> 12:27 9
> 12:28 9
> 12:29 9
>
> 29 rows selected.
>
> SQL>
> SQL> select c_time from
> 2 (
> 3 select
> 4 to_char(c_time,'hh:mi') c_time,
> 5 volume,
> 6 lag (volume,1) over (order by c_time) previous_1,
> 7 lag (volume,2) over (order by c_time) previous_2,
> 8 lag (volume,3) over (order by c_time) previous_3,
> 9 lag (volume,4) over (order by c_time) previous_4,
> 10 lag (volume,5) over (order by c_time) previous_5,
> 11 lag (volume,6) over (order by c_time) previous_6,
> 12 lag (volume,7) over (order by c_time) previous_7
> 13 from data_collector
> 14 order by c_time
> 15 )
> 16 where volume = previous_1
> 17 and previous_1 = previous_2
> 18 and previous_1 = previous_3
> 19 and previous_1 = previous_4
> 20 and previous_1 = previous_5
> 21 and previous_1 = previous_6
> 22 and previous_1 = previous_7;
>
> C_TIM
> -----
> 12:14
> 12:27
> 12:28
> 12:29
>
>
Received on Sun Feb 23 2003 - 14:01:03 CST