Thanks for your awesome help on "Trying to split serial number ranges" question today.
I have another opportunity to ask a question and it's somewhat related to my earlier question.
I hope this would be an easy one.
I have a table with the following numbers
1
2
3
4
5
6
7
10
13
14
15
16
17
18
25
26
27
28
29
30
32
33
34
I need to have an output as shown below
1-7
10-10
13-18
25-30
32-34
Please note, that 1 through 7 are consecutive and hence I need MIN and MAX for this range of numbers. Next comes 10 and since the next number 13 is not consecutive, my MIN and MAX should both be 10 and 10 and so on.. So basically, I need to group these numbers
if they are consecutive and then get MIN and MAX for that set of numbers. I am able to viusualize this but cannot put it in SQL.
Thanks
Ahh, zen and the art of SQL -- once you get into the zone, you can see it all.
Well, this is a simple variation on that first theme (glad you asked the other one first, made this one a 5 second answer!)
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:7866543931332 <code>
was the first answer and this just builds on it - almost the same query (except for the addition of the cartesian product with dual that is)
ops$tkyte@ORA920> select max( decode(mod(rn,2),1,x,null) ),
2 max( decode(mod(rn,2),0,x,null) )
3 from (
4 select x, row_number() over ( order by x ) rn
5 from (
6 select *
7 from (
8 select x,
9 nvl(lag(x) over (order by x)+1,-1) last_x,
10 nvl(lead(x) over (order by x)-1,-1) next_x
11 from t
12 ), ( select 1 xx from dual
13 union all
14 select 2 xx from dual )
15 )
16 where (x <> last_x and xx = 1) OR (x <> next_x and xx = 2)
17 )
18 group by ceil(rn/2)
19 /
MAX(DECODE(MOD(RN,2),1,X,NULL)) MAX(DECODE(MOD(RN,2),0,X,NULL))
------------------------------- -------------------------------
1 7
10 10
13 18
25 30
32 34
ops$tkyte@ORA920>
I'll leave it to the jedi sql guys to explain it. just feel the "force" ;)
analytics rock and roll don't they....
The explaination is hidden in the other answer -- the cartesian product and modified where clause:
16 where (x <> last_x and xx = 1) OR (x <> next_x and xx = 2)
was just to get the even number of rows I needed for the row_number pivot to work right and get those single digit ranges working -- in fact you could add that to the other answer to get 99-99 to pop out for you.