All Forums General
m.tahoon 43 posts Joined 09/11
20 Mar 2016
Columns Transformation to Sets boundaries

 

i have a table with the following values for Sets of values, need to generate teh start and the end of each set

 

Values

~~~~~

1

2

3

4

5

11

12

13

14

15

16

17

 

i want to get a query perform transformation for the range of each consecutive set:

Desired output:

 

Range_Start      Range_End

~~~~~~~~     ~~~~~~~

1 5

11 17

 

dnoeth 4628 posts Joined 11/04
20 Mar 2016

If the values are unique you can utilize that both your value column and a ROW_NUMBER are sequential:

SELECT
  MIN(seqval) AS StartVal,
  MAX(seqval) AS EndVal,
  COUNT(*)
FROM
 (
  SELECT
    -- assign the same grp as long as there's no gap
    seqval - ROW_NUMBER() OVER (ORDER BY seqval ASC) AS grp,
    seqval
  FROM seqtest
 ) dt
GROUP BY grp
;

If values are non-unique you must switch to DENSE_RANK instead...

 

Dieter

You must sign in to leave a comment.