Re: how to create query ??????????
From: AK <ak_tiredofspam_at_yahoo.com>
Date: 17 Jan 2004 12:48:37 -0800
Message-ID: <46e627da.0401171248.23986b03_at_posting.google.com>
(SELECT END_NO, (ROW_NUMBER() OVER(ORDER BY END_NO)) AS SERIES_NUM
FROM INTERVALS I WHERE NOT EXISTS(SELECT START_NO FROM INTERVALS I1 WHERE I.END_NO+1=I1.START_NO)) AS HIGHER_ENDS 2. Join them on SERIES_NUM
Date: 17 Jan 2004 12:48:37 -0800
Message-ID: <46e627da.0401171248.23986b03_at_posting.google.com>
Assuming that the intervals do not overlap, we can also use ROW_NUMBER() and avoid recursion:
- Create 2 table expressions:
(SELECT START_NO, (ROW_NUMBER() OVER(ORDER BY START_NO)) AS SERIES_NUM
FROM INTERVALS I WHERE NOT EXISTS(SELECT END_NO FROM INTERVALS I1 WHERE I1.END_NO+1=I.START_NO)) AS LOWER_ENDS
(SELECT END_NO, (ROW_NUMBER() OVER(ORDER BY END_NO)) AS SERIES_NUM
FROM INTERVALS I WHERE NOT EXISTS(SELECT START_NO FROM INTERVALS I1 WHERE I.END_NO+1=I1.START_NO)) AS HIGHER_ENDS 2. Join them on SERIES_NUM
SELECT START_NO, END_NO
FROM LOWER_ENDS JOIN HIGHER_ENDS
ON LOWER_ENDS.SERIES_NUM = HIGHER_ENDS.SERIES_NUM
Received on Sat Jan 17 2004 - 21:48:37 CET