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@posting.google.com>

Assuming that the intervals do not overlap, we can also use ROW_NUMBER() and avoid recursion:

1. 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 - 14:48:37 CST