I need better things to do during my lunch break...
Lars, here's another solution...
select level, nums
from (
select a.nums,
(select decode(count(*),0,1,1,0,-1)
from phones x
where to_number(replace(x.nums,'-'))=a.nums-1) cnt
from (
select to_number(replace(nums,'-')) nums
from phones
) a,
(
select to_number(replace(nums,'-')) nums
from phones
) b
where a.nums+1 = b.nums
)
where level <= 10
start with cnt = 1
connect by prior nums = nums-1;
LEVEL NUMS
--------- ----------
1 5051002
2 5051003
3 5051004
4 5051005
5 5051006
6 5051007
7 5051008
8 5051009
9 5051010
10 5051011
1 5052001
2 5052002
3 5052003
4 5052004
5 5052005
6 5052006
7 5052007
8 5052008
9 5052009
10 5052010
-----Original Message-----
From: Anthony Molinaro
[mailto:oracledba-ezmlmshield-x60532404.[Email address protected]
Sent: Friday, December 03, 2004 10:37 AM
To: LazyDBA Discussion
Subject: RE: URGENT. How do i find continuous number i a column??
Lars,
There's a few ways to do this. This is a problem that brings out
the fun side of sql; many people will look at this problem and provide
different answers.
Ok, here's my shot at it. I believe there's a smarter (shorter) way to
do this, so, hopefully there'll be a lot of followups.
You didn't provide any test data to work with, so I just made some
up...
create table phones as select cast(5051001+rownum as varchar2(8)) nums
from all_objects where rownum <= 15;
update phones set nums = replace(nums,'505','505-');
insert into phones select to_char(5052000+rownum) from all_objects where
rownum <= 13;
update phones set nums = replace(nums,'505','505-') where
instr(nums,'-') = 0
select * from phones;
NUMS
--------
505-1002
505-1003
505-1004
505-1005
505-1006
505-1007
505-1008
505-1009
505-1010
505-1011
505-1012
505-1013
505-1014
505-1015
505-1016
505-1017
505-1019
505-2001
505-2002
505-2003
505-2004
505-2005
505-2006
505-2007
505-2008
505-2009
505-2010
505-2011
505-2012
505-2013
30 rows selected.
/* ok, so, we have a nice spread of data now...
We have our first set of 10 (505-1002 to 505-1011).
505-1012 - 505-1017 is consecutivem but is not 10 numbers,
so,those numbers will not be in the final answer.
505-2001 to 505-2010 are consecutive and are
10 numbers so those will be in the final answer.
505-2011 to 505-2013 will not be in the final answer */
/* what I find easiest is to convert the phone numbers in to real
numbers, and find all the consecutives */
select to_number(replace(nums,'-')) nums from phones;
NUMS
-------
5051002
5051003
5051004
...
/* ok, that converts to numbers, now, keep only consecutive values */
select a.nums
from (
select to_number(replace(nums,'-')) nums
from phones
) a,
(
select to_number(replace(nums,'-')) nums
from phones
) b
where a.nums+1 = b.nums;
NUMS
---------
5051002
5051003
5051004
5051005
5051006
5051007
5051008
5051009
5051010
5051011
5051012
5051013
5051014
5051015
5051016
5052001
5052002
5052003
5052004
5052005
5052006
5052007
5052008
5052009
5052010
5052011
5052012
/* the results now only contain rows that have consective values
following them
the next step is to group the values. This is tricky, so it's easiest
to put
all consecutive values into a group until there's a non-consecutive
value,
that will start a new group. So, 5051002 to 5051016 will be in one
group
and 5052001 to 5052012 will be in another.
The way to determine what values belong to which group is to check if
the value before
the current value is one less than the current value, if it's not,
then we start a new group. */
select a.nums,
decode(lag(a.nums)over(order by a.nums),a.nums-1,0,1) x
from (
select to_number(replace(nums,'-')) nums
from phones
) a,
(
select to_number(replace(nums,'-')) nums
from phones
) b
where a.nums+1 = b.nums;
NUMS X
------- ----------
5051002 1
5051003 0
5051004 0
5051005 0
5051006 0
5051007 0
5051008 0
5051009 0
5051010 0
5051011 0
5051012 0
5051013 0
5051014 0
5051015 0
5051016 0
5052001 1
5052002 0
5052003 0
5052004 0
5052005 0
5052006 0
5052007 0
5052008 0
5052009 0
5052010 0
5052011 0
5052012 0
/* the 1's represent new groups, now we can just sum to put the rows
into their respective groups */
select nums,
sum(x) over(order by nums) s
from (
select a.nums,
decode(lag(a.nums)over(order by a.nums),a.nums-1,0,1) x
from (
select to_number(replace(nums,'-')) nums
from phones
) a,
(
select to_number(replace(nums,'-')) nums
from phones
) b
where a.nums+1 = b.nums
);
NUMS S
-------- ----------
5051002 1
5051003 1
5051004 1
5051005 1
5051006 1
5051007 1
5051008 1
5051009 1
5051010 1
5051011 1
5051012 1
5051013 1
5051014 1
5051015 1
5051016 1
5052001 2
5052002 2
5052003 2
5052004 2
5052005 2
5052006 2
5052007 2
5052008 2
5052009 2
5052010 2
5052011 2
5052012 2
/* ok, so, the hardwork is done, now we just need to take the first 10
in each group and that's it! */
select nums
from (
select nums,
row_number() over(partition by s order by nums) rn
from (
select nums,
sum(x) over(order by nums) s
from (
select a.nums,
decode(lag(a.nums)over(order by a.nums),a.nums-1,0,1) x
from (
select to_number(replace(nums,'-')) nums
from phones
) a,
(
select to_number(replace(nums,'-')) nums
from phones
) b
where a.nums+1 = b.nums
)
)
)
where rn <= 10;
NUMS
--------
5051002
5051003
5051004
5051005
5051006
5051007
5051008
5051009
5051010
5051011
5052001
5052002
5052003
5052004
5052005
5052006
5052007
5052008
5052009
5052010
Hope that helps,
- ant
-----Original Message-----
From: Lars Baad-Jensen [mailto:oracledba-ezmlmshield-x81110780.[Email
address protected]
Sent: Friday, December 03, 2004 8:20 AM
To: LazyDBA Discussion
Subject: URGENT. How do i find continuous number i a column??
Hi gurus,
I need to find continuous numbers in rows in a table.
I need to find sets of 10 numbers.
It's phonenumbers, that i need to find sets of. The phonenumber starts
with 505 and is 8 charachter long. The phonenumber column i varchar
datatype.
Hope you can help me
Lars
Denmark
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page