Another approach
Dave, April 01, 2002 - 10:32 am UTC
Since one of the virtues of a good programmer is laziness, I ripped off some of Tom's own code to produce a little routine which provides you with a list of numbers ...
create type tab_Integer as table of Number(30);
/
create or replace function FromToIncr_2_Table
(
p_start in number,
p_end in number,
p_incr in number
)
return tab_Integer
as
l_element number;
l_table tab_Integer := tab_Integer();
begin
l_element := p_start;
loop
exit when l_element > p_end;
l_table.extend;
l_table(l_table.count) := l_element;
l_element := l_element+p_incr;
end loop;
return l_table;
end;
/
select * from table(cast(FromToIncr_2_Table(1,7,1) as tab_Integer));
COLUMN_VALUE
------------
1
2
3
4
5
6
7
7 rows selected.
create table customer (key number,name varchar2(32));
insert into customer values (1,'Smith');
insert into customer values (3,'Jones');
insert into customer values (5,'Willy Wonka');
select * from customer;
KEY NAME
---------- --------------------------------
1 Smith
3 Jones
5 Willy Wonka
select t1.key
from customer c,
(select column_value key
from table(cast(FromToIncr_2_Table(1,5,1) as tab_Integer))) t1
where t1.key = c.key(+)
and c.key is null
/
KEY
----------
2
4
Hey, it's not perfect, and you'd have to do something about working out the upper and lower limits (it could be done in the function itself) but it's an approach that might be flexible enough to extend to other problem types. Although it uses PL/SQL it does sort-of hide the fact.
Note the clever lack of any error handling in the function as well. You might like to do something about that to avoid infinite result sets etc.
Oh, and thanks for the code Tom.
Dave's approach better...
malcolm, April 01, 2002 - 4:03 pm UTC
...because it will work for gaps in the key greater than one.
i.e.
SQL> select * from test;
X
----------
1
2
4
5
8
SQL> select x+1
2 from test t
3 where NOT EXISTS ( select null
4 from test t2
5 where t2.x = t.x+1 )
6
SQL>
SQL> /
X+1
----------
3
6
9
No 7!
April 01, 2002 - 8:40 pm UTC
so -- you get a key at a time don't you. If you get 6, you'll use 6 and the very next time you ask -- you'll get 7.
Don't over complicate I say -- the query is what you want, the procedural code -- while interesting -- is overdone (IMO).
Well, anyway, this whole discussion is overdone. I'm pretty sure everyone can tell my opinion on this "gap free" sequence concept.
(one of the constraints given also was "thou shalt not use plsql")...
Dave again, April 01, 2002 - 10:50 pm UTC
I agree that a gapless sequence is a purposeless thing as far as relational databases are concerned, but searching for gaps in sequences would be a routine run by auditors to find missing invoice numbers etc., so there is often a functional need for these.
The restriction on not using PL/SQL is pretty common when you find yourself lumbered with a COTS reporting package, for example, but the prohibition is generally based just on the need for a SQL method of accessing the data. I think it's important to expose people to a technique like this just in case is drops right into place for their purposes. Of course if it's just a foundless prejudice against PL/SQL then "suck it up" buddy, and get coding.
Anyway, it seems like no schema is complete without a handy IOT of integers from 1 to a few million just for doing handy outer-joins in cases like this.
April 02, 2002 - 7:56 am UTC
For auditors, I would just do this if they really really made me:
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( x int );
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t select rownum from all_users where rownum <= 20;
20 rows created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> delete from t where x in ( 3, 4, 5, 15, 16 );
5 rows deleted.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;
Commit complete.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> select 'missing ' || (x+1) || '..' || (next_x-1)
2 from ( select x, lead(x) over ( order by x ) next_x
3 from t
4 )
5 where x+1 <> next_x
6 /
'MISSING'||(X+1)||'..'||(NEXT_X-1)
------------------------------------------------------------------------------------------
missing 3..5
missing 15..16
ops$tkyte@ORA817DEV.US.ORACLE.COM>
Agreed, I keep a dummy table around with lots of numbers in it -- but tend to use all_objects for most cases.
Dave, April 02, 2002 - 8:49 am UTC
That's a very nice solution for a readable report. Imight just add an ORDER BY x at the end.
Gap free
A reader, April 02, 2002 - 10:17 am UTC
Hi,
Suppose those idoit "boss" or system want a gap free
invoice number, what is the solution beside keeping
a "unique" number table? Or what we should told
that "boss" without quit the job?
I know you can do anything you want in ORACLE.
Thanks
April 02, 2002 - 10:39 am UTC
You can do this "gap free" thing -- it'll have these side effects:
o your system will serialize. one person at a time.
o you will end up with gaps if you ever delete and will have to use ugly queries or logic like the above to achieve your goal
Me -- I question the business logic behind the supposed "requirement". But then again, I question lots of stuff.
will this help
Venkatesa Prasad. N, April 05, 2002 - 12:15 pm UTC
Hi Tom,
To get a generalised query for missing numbers, we use the following query.
SELECT a.field + (b.val - 1)
FROM (SELECT MIN (inv_no) inv_no
FROM tab
WHERE <condition>
) a,
(SELECT ROWNUM val
FROM tab
WHERE ROWNUM <= (SELECT MAX (field) - MIN (field) + 1
FROM tab
WHERE <condition>
)
) b
MINUS
SELECT field
FROM tab
WHERE <condition>
update the missing numbers
Tanmoy Datta Choudhury, April 18, 2003 - 8:54 am UTC
now here goes another query..though the topic mention in this question finding the missing number is unnecessary its a puzzle and a really really good one ....and thanks TOM once again for a great answer ...
i have another with me trying to crack it ..here it goes ..
table t having two column a and b and their values r like this ...
rows a b
1 2
2 3 3
3 4
4
5 2 9
rows just to mention the row numbers in table t...
so we see 4 number rows having NULL for both a and b ...
now the requirment is to update the NULL columns of a and b and replace with some number say 1 in a update statement without writting a pl/sql or replace func ...possible????
tom any suggesstion ??
April 18, 2003 - 12:20 pm UTC
update t set a = decode( a, null, 1, a ), b = decode( b, null, 1, b );
update the missing numbers
Tanmoy Datta Choudhury, April 18, 2003 - 8:54 am UTC
now here goes another query..though the topic mention in this question finding the missing number is unnecessary its a puzzle and a really really good one ....and thanks TOM once again for a great answer ...
i have another with me trying to crack it ..here it goes ..
table t having two column a and b and their values r like this ...
rows a b
1 2
2 3 3
3 4
4
5 2 9
rows just to mention the row numbers in table t...
so we see 4 number rows having NULL for both a and b ...
now the requirment is to update the NULL columns of a and b and replace with some number say 1 in a update statement without writting a pl/sql or replace func ...possible????
tom any suggesstion ??
How about NVL instead of DECODE
A reader, April 18, 2003 - 4:28 pm UTC
Tom,
How about
update t set a = nvl(a, 1), b = nvl(b,1);
instead of
update t set a = decode( a, null, 1, a ), b = decode( b, null, 1, b );
Is there any reason you used DECODE instead of NVL?
April 18, 2003 - 4:31 pm UTC
sure, or case or <probably infinite number of ways> .....
don't know why but I rarely use NVL(), I think I find decode more "expressive"
missing mumbers
bhavin, May 19, 2003 - 10:26 am UTC
the reply was great. it solved my problem. thanks a lot.
not working accurate
omer, January 15, 2004 - 2:46 am UTC
I agree with ur comments about gaps, but i m also facing the same problem.
In Human resource system I have to find absents day of employee in a month e.g.
Attendance of ali
03-dec-03
04-dec-03
and so on
He has not come on 1st and 2nd december.
Ur last query does not work in this criteria
if u plz help in this problem? I can use pl/sql.
regards
January 15, 2004 - 3:28 am UTC
wow -- your keyboard is failing in a horrible way, very important letters (mostly vowels it seems) are not getting from your keyboard into your computer. might want to check that out.
just outer join that hr set of records to a set of records that have the dates of interest to you. consider (you can generate the set of dates - my "X" subquery - using any table with 31 or more records of course)
ops$tkyte@ORA9IR2> create table t ( empno number, dt date );
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t values ( 1234, '03-dec-2003' );
1 row created.
ops$tkyte@ORA9IR2> insert into t values ( 1234, '04-dec-2003' );
1 row created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> variable x refcursor
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
2 l_date date := to_date( '01-dec-2003', 'dd-mon-yyyy' );
3 l_empno number := 1234;
4 begin
5 open :x for
6
7 select l_empno, decode( t.dt, null, 'Absent' ), x.dt
8 from ( select l_date+rownum-1 dt
9 from (select 1
10 from dual
11 group by cube(1,2,3,4,5) )
12 where rownum <= to_char(last_day(l_date),'dd') ) x,
13 ( select *
14 from t
15 where empno = l_empno ) t
16 where x.dt = t.dt (+)
17 ;
18 end;
19 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> print x
:B1 DECODE DT
---------- ------ ---------
1234 Absent 01-DEC-03
1234 Absent 02-DEC-03
1234 03-DEC-03
1234 04-DEC-03
1234 Absent 05-DEC-03
1234 Absent 06-DEC-03
1234 Absent 07-DEC-03
1234 Absent 08-DEC-03
1234 Absent 09-DEC-03
1234 Absent 10-DEC-03
1234 Absent 11-DEC-03
1234 Absent 12-DEC-03
1234 Absent 13-DEC-03
1234 Absent 14-DEC-03
1234 Absent 15-DEC-03
1234 Absent 16-DEC-03
1234 Absent 17-DEC-03
1234 Absent 18-DEC-03
1234 Absent 19-DEC-03
1234 Absent 20-DEC-03
1234 Absent 21-DEC-03
1234 Absent 22-DEC-03
1234 Absent 23-DEC-03
1234 Absent 24-DEC-03
1234 Absent 25-DEC-03
1234 Absent 26-DEC-03
1234 Absent 27-DEC-03
1234 Absent 28-DEC-03
1234 Absent 29-DEC-03
1234 Absent 30-DEC-03
1234 Absent 31-DEC-03
31 rows selected.
trigger
A reader, January 15, 2004 - 6:34 am UTC
If those missing keys are so important, why not insert them in another table IF you delete/change them. Next time you want to use a new one pick smallest from that table and delete it, if none generate next.
just another thought
terrible requirement for sure ... but
Gabriel, January 15, 2004 - 1:03 pm UTC
Talking about solutions ...
Dave's pl/sql approach, for the mentioned NUMBER(8) column, would use rather too much memory to load that integer array + local array ==> re-loaded with every single call + multiple sessions ==> even more memory. It will bring all the missing keys for sure ... but, is there a need for more than a handful? The mentioned IOT would help ... although at 99,999,999 rows ... rather large.
Assuming the range [1-100] ... Tom's SQL ...
select t1.x+1
from (select x from t) t1
where NOT EXISTS ( select null
from (select x from t) t2
where t2.x = t1.x+1 )
should probably be expanded a bit to ...
select t1.x+1
from (select x from t union select 0 x from dual) t1
where NOT EXISTS ( select null
from (select x from t union select 0 x from dual) t2
where t2.x = t1.x+1 )
and t1.x < 100
in order to ensure:
1. a return value when t is empty
2. when mising, the lowest value in the range is also returned
3. a value greater than the highest value in the range is not returned
Similar considerations for the analytic query ...
select 'missing '||to_char(x+1)||'..'||to_char(next_x-1)
from ( select x, lead(x) over ( order by x ) next_x from t )
where x+1 <> next_x
;
tiny enhancement ...
select 'missing '||to_char(x+1)||'..'||to_char(next_x-1)
from ( select x, lead(x) over ( order by x ) next_x
from (select x from t union select 0 x from dual union select 100 x from dual)
)
where x+1 <> next_x
;
Thx.
0 for No rows in EMP Table
Rajashekhar GANGA, April 05, 2004 - 5:44 am UTC
Dear Tom
we have a martrix report with Dept and Job Title as rows and columns
for Dept which does not have a specfic job title emp. there
should be a 0 in the cell.
ex:
Research Dept has no Sales Persons so, that cell should show 0.
i have come with the following solution,
can please suggest a better one than this
....
select b.deptno||'-'||dname dept,a.job,
(select count(x.empno) from emp x where
deptno=b.deptno and job=a.job) no_of_emp
from
(select distinct job from emp ) a,dept b
Thanks for Reading my Problem.
Raj GANGA
April 05, 2004 - 9:42 am UTC
well, you cannot dynamically "make up the columns" so joining to the distinct jobs doesn't make sense.
you can QUERY up the distinct jobs in a query and then dynamically build a sql statement that looks like this:
select deptno,
count( decode( job, 'JOB1', empno ) ) JOB1,
count( decode( job, 'JOB2', empno ) ) JOB2,
....
count( decode( job, 'JOBn', empno ) ) JOBn
from emp
group by deptno;
where JOB1, JOB2, ... JOBn are the names of your jobs.
group by cube()
A reader, September 20, 2004 - 4:55 pm UTC
9 from (select 1
10 from dual
11 group by cube(1,2,3,4,5))
What is the significance of using the cube extension to group by in your query above?
Isnt the above just the same as
select 1 from all_objects where rownum<=25
Thanks
September 20, 2004 - 7:12 pm UTC
it automagically makes as many rows as you care to make is all.
there are many ways to do that. from the model clause in 10g, to all_objects (which is limited in the number of rows you return, unless you start to cartesian product it), to a pipelined function, to this.
Its missing for a reason...
Bob B, September 20, 2004 - 8:27 pm UTC
A couple other points
<RANT START>
1) Its probably missing for a reason. Lets assume its a sequence of invoices. Invoices 1, 2, and 3 are inserted. Invoice 2 is deleted. A new invoice is inserted as invoice 2. Any references to invoice 2 that were not enforced by FKEY (which is bad design anyway) will now be associated with invoice 2. If later on, the user decides that deleting invoice 2 was a mistake and that they want it back as invoice 2, what do you do? Do you move the current invoice 2 elsewhere and put the old invoice 2 back. Do you put the old invoice 2 in the next location. Either way, you'd probably end up needing some sort of weird mapping table (ick).
2) I think the serialization is even worse than Tom first mentioned. I don't know if any inserts on this table do more than one row at a time, but if they do, you'll probably need to write your own serialization device. You'll have to do something like: lock table -> get next n ids (vacant keys first, if available) -> insert next n ids -> unlock table. Other than the table lock, this would be code that you'd have to write and maintain in order for things to work properly
3) After all this work, you will STILL have gaps. The only way to ensure no gaps would occur is to actually renumber the ids each time a row is deleted. That's kind of like if the US said that each time someone died, they have to reassign each person's SSN and that no one could die or be born until the reassignment was done.
I wrote this little script up so you could see how well this approach can scale. Mind you, there is no real serialization to lock the table while its getting the next id in this example. Then again, that doesn't matter since its a single user test.
CREATE TABLE PATIENCE_TESTER(
ID NUMBER(8),
PRIMARY KEY( ID )
);
/
DECLARE
v_next_id PATIENCE_TESTER.ID%TYPE := NULL;
v_max_idx BINARY_INTEGER := 10000;
BEGIN
FOR c_indx IN 1 .. v_max_idx LOOP
SELECT MIN( ID ) + 1
INTO v_next_id
FROM ( SELECT ID FROM PATIENCE_TESTER UNION ALL SELECT 0 FROM DUAL ) t1
WHERE NOT EXISTS (
SELECT NULL
FROM PATIENCE_TESTER t2
WHERE t2.ID = t1.ID+1
);
INSERT INTO PATIENCE_TESTER ( ID ) VALUES ( v_next_id );
IF MOD( c_indx, 10 ) = 0 THEN
v_next_id := MOD( ABS( DBMS_RANDOM.RANDOM() ), c_indx ) + 1;
DELETE
FROM PATIENCE_TESTER t1
WHERE t1.ID = v_next_id;
END IF;
END LOOP;
END;
/
<RANT END>
- BB
September 20, 2004 - 8:57 pm UTC
thanks :)
in clause query
devarshi, September 22, 2004 - 4:23 am UTC
I have table t like this
select * from t;
empname deptno
aa 10
ab 10
bb 20
bc 20
cc 30
da 50
db 50
i submit a query like this
select count(*),deptno from t where deptno in (10,20,40)
group by dept no;
The result set i want is
2 10
2 20
40
the result i get is
2 10
2 20
is there a way to create a null line for non existent deptno
thanks
September 22, 2004 - 8:14 am UTC
I would say "you are missing a table". You need to join here -- there must be a parent table DEPT somewhere.
Here are various implementations with and without this parent table (but the parent table is perhaps the 'correct' answer)
scott@ORA9IR2> select dept.deptno, count( emp.deptno )
2 from dept, emp
3 where dept.deptno in ( 10, 20, 40 )
4 and dept.deptno = emp.deptno(+)
5 group by dept.deptno;
DEPTNO COUNT(EMP.DEPTNO)
---------- -----------------
10 3
20 5
40 0
scott@ORA9IR2>
scott@ORA9IR2>
scott@ORA9IR2> select deptno, (select count(*) from emp where emp.deptno = dept.deptno)
2 from dept
3 where deptno in ( 10, 20, 40 );
DEPTNO (SELECTCOUNT(*)FROMEMPWHEREEMP.DEPTNO=DEPT.DEPTNO)
---------- --------------------------------------------------
10 3
20 5
40 0
scott@ORA9IR2>
scott@ORA9IR2>
scott@ORA9IR2> create or replace type myArray as table of number
2 /
Type created.
scott@ORA9IR2>
scott@ORA9IR2> select x.column_value, count(emp.deptno)
2 from TABLE( myArray(10,20,40) ) x, emp
3 where x.column_value = emp.deptno(+)
4 group by x.column_value;
COLUMN_VALUE COUNT(EMP.DEPTNO)
------------ -----------------
10 3
20 5
40 0
scott@ORA9IR2>
scott@ORA9IR2>
scott@ORA9IR2> select x.deptno, count(emp.deptno)
2 from (select 10 deptno from dual union all select 20 deptno from dual union all select 40 deptno from dual )x,
3 emp
4 where x.deptno = emp.deptno(+)
5 group by x.deptno;
DEPTNO COUNT(EMP.DEPTNO)
---------- -----------------
10 3
20 5
40 0
millions of fingers, millions of thumbs...
Scott Martin, September 23, 2004 - 1:03 am UTC
While we are showing off the generation of ordinals, I wanted to submit my entry.
create table ot (c1 number);
insert into ot values(0);
insert into ot values(1);
commit;
create or replace view o4 as
select o0.c1 + o1.c1 * 2 c1 from ot o0, ot o1;
create or replace view o16 as
select o0.c1 + o1.c1 * 4 c1 from o4 o0, o4 o1;
create or replace view o256 as
select o0.c1 + o1.c1 * 16 c1 from o16 o0, o16 o1;
create or replace view o65536 as
select o0.c1 + o1.c1 * 256 c1 from o256 o0, o256 o1;
create or replace view o4T as
select o0.c1 + o1.c1 * 65536 c1 from o65536 o0, o65536 o1;
That will get ya the ordinals from 0 to 4,294,967,295 pretty succinctly. Of course you would want to populate an IOT with however many you needed. One surely would not want to scan all 4 trillion numbers looking for those between, say, 100 and 110. I'm not saying this is any better than
select rownum from all_objects o1, all_objects o2;
Just different. Besides, it's fun to put Oracle in a 32 way self join.
SCott.
in clause
devarshi, September 23, 2004 - 9:00 am UTC
thanks a lot . I was wondering if we could do it without a table ,now i know we cannot thanks
Little addition to query
Madhuri, June 13, 2005 - 6:26 am UTC
select t5.x as l_range, min(t6.x)-1 as u_range from
(select x+1 as x
from t t1
where NOT EXISTS (select null
from t t2
where t2.x = t1.x+1)
) t5
, t t6
where t6.x > t5.x
group by t5.x
Fill in the gaps 10g
Alex, March 28, 2006 - 1:59 pm UTC
another way, to extend the possible solutions with
the dual-level trick found elsewhere here in asktom.
SQL> SELECT l employee_id, NVL (e.last_name, 'deleted') last_name
2 FROM EMPLOYEES e,
3 (SELECT *
4 FROM (SELECT LEVEL l
5 FROM DUAL
6 CONNECT BY LEVEL <= (SELECT MAX (employee_id)
7 FROM EMPLOYEES))
8 WHERE l >= (SELECT MIN (employee_id)
9 FROM EMPLOYEES)) l
10 WHERE e.employee_id(+) = l.l
11 ORDER BY l;
EMPLOYEE_ID LAST_NAME
----------- -------------------------
100 King
101 Kochhar
102 De Haan
103 Hunold
104 Ernst
105 deleted
106 deleted
107 Lorentz
108 deleted
109 deleted
110 deleted
...
Finding Gaps in million record table
Tarun, December 09, 2009 - 4:07 am UTC
Hi Tom,
How to find gaps in column if my table have millions of records.
for e.g. i have CUSTOMER table which contain customers data(INVOICE_NO) in different store(STORE_NO) and have millions of records and it is partition into 10 different partitions and each store can be in any of the partition.
I need to find all missing INVOICE_NO in a particular store.
e.g.
STORE_NO INVOICE_NO
1 1
1 1000
2 50
2 5000
output should be
STORE_NO MISSING_FROM MISSING_TO
1 2 999
2 1 49
2 51 4999
The solution can be in sql or in pl/sql.
Thanks!! in advance for your help!!!
December 10, 2009 - 1:35 pm UTC
this was a 3.4 million row table whereby MOST of the things had gaps
ops$tkyte%ORA10GR2> select store_no, invoice_no, next_invoice_no,
2 invoice_no+1 missing_from, next_invoice_no-1 missing_to
3 from (
4 select owner store_no,
5 id invoice_no,
6 lead(id) over (partition by owner order by object_id) next_invoice_no
7 from big_table.big_table
8 )
9 where invoice_no <> next_invoice_no+1
10 /
3499607 rows selected.
Muliple values are missing in serial
Girish, December 22, 2009 - 10:39 pm UTC
Hi Tom,
This works fine if there is only one missing values
for example 1,2,4,6 only 3,5 are missing.
But when I executed this query when multiple values are missing it was not showing few values missing
for example 1,2,5,8 here 3,4 are missing between 2 and 5.similarly 6,7 are missing between 5 and 8.
How to display these missing key values
Regards & Thanks,
Girish
December 31, 2009 - 11:48 am UTC
it shows you the value that is not followed by a value one greater, that is what it was designed to do.
the query RIGHT above your comment show you how to get those ranges - did you look at it?
find ranges
sagi, December 28, 2010 - 12:40 pm UTC
Hi Tom,
I would like to get ranges
the data is
2
3
5
6
7
10
I would like to get
2-3
5-7
10-10
10x,
Sagi
December 28, 2010 - 3:21 pm UTC
ops$tkyte%ORA11GR2> create table t ( x int primary key );
Table created.
ops$tkyte%ORA11GR2> insert into t
2 select 2 from dual union all
3 select 3 from dual union all
4 select 5 from dual union all
5 select 6 from dual union all
6 select 7 from dual union all
7 select 10 from dual
8 /
6 rows created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select min(x), max(x)
2 from (
3 select x, last_value(grp ignore nulls) over (order by x) new_grp
4 from (
5 select x,
6 decode( nvl(lag(x) over (order by x),x), x-1, to_number(null), row_number() over (order by x) ) grp
7 from t
8 )
9 )
10 group by new_grp
11 order by new_grp
12 /
MIN(X) MAX(X)
---------- ----------
2 3
5 7
10 10
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into t values ( 11 );
1 row created.
ops$tkyte%ORA11GR2> insert into t values ( 0 );
1 row created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select min(x), max(x)
2 from (
3 select x, last_value(grp ignore nulls) over (order by x) new_grp
4 from (
5 select x,
6 decode( nvl(lag(x) over (order by x),x), x-1, to_number(null), row_number() over (order by x) ) grp
7 from t
8 )
9 )
10 group by new_grp
11 order by new_grp
12 /
MIN(X) MAX(X)
---------- ----------
0 0
2 3
5 7
10 11
assumes X is unique, if not DISTINCT it first.
The sql does not work if some consecutive numbers are missing
Aveek, December 29, 2010 - 3:50 am UTC
Hi Tom,
This query does not work if some consecutive numbers are missing, how to get over this problem with only sql
Regards,
Aveek
December 30, 2010 - 1:15 pm UTC
explain what you mean - the example as posted has consecutive missing numbers as pointed out below.
Aveek,
Sokrates, December 30, 2010 - 1:44 am UTC
8 and 9 are missing, but it works, so
?
group by cube
Praveen Ray, January 06, 2011 - 4:52 am UTC
Hi Tom,
Please, help me understand how the following code is working.
select null from dual group by cube(1, 2, 3, 4, 5);
I need an explanation in your style so that I can guess what happens if I provide not null values for COLUMN2 and COLUMN5. And, what if different different values, and ...
Regards,
Ray.
January 06, 2011 - 8:21 am UTC
easiest to understand with some real data:
ops$tkyte%ORA11GR2> select l1, l2, count(*),
2 grouping(l1), grouping(l2)
3 from (select 1 l1, 2 l2, 3 l3, 4 l4, 5 l5 from dual)
4 group by cube(l1,l2)
5 /
L1 L2 COUNT(*) GROUPING(L1) GROUPING(L2)
---------- ---------- ---------- ------------ ------------
1 1 1
2 1 1 0
1 1 0 1
1 2 1 0 0
ops$tkyte%ORA11GR2> select l1, l2, l3, count(*),
2 grouping(l1), grouping(l2), grouping(l3)
3 from (select 1 l1, 2 l2, 3 l3, 4 l4, 5 l5 from dual)
4 group by cube(l1,l2,l3)
5 /
L1 L2 L3 COUNT(*) GROUPING(L1) GROUPING(L2) GROUPING(L3)
---------- ---------- ---------- ---------- ------------ ------------ ------------
1 1 1 1
3 1 1 1 0
2 1 1 0 1
2 3 1 1 0 0
1 1 0 1 1
1 3 1 0 1 0
1 2 1 0 0 1
1 2 3 1 0 0 0
8 rows selected.
the group by cube creates all possible rollups. The first row of the first result set is the "super total" - we aggregated over ALL values of L1, L2. That would be the same as:
select null, null, count(*) from t /* NO GROUP BY AT ALL */
The second row is the aggregate over L1 by L2 - it would be equivalent to
select null, l2, count(*) from t group by l2;
the third row is
select l1, null, count(*) from t group by l1;
and the fourth is
select l1, l2, count(*) from t group by l1,l2;
In the second query - we get 8 rows because if you have 3 columns - each with one value - you have 2^3 outcomes.
If you did 4 columns - you would get 16 rows - 2^4.
You should note that group by cube(1,2,3,4,5) is not asking the database to group by the 1st column, then the 2nd column and so on - group by doesn't use ordinal column numbers like order by does. It is grouping by the CONSTANT 1, the CONSTANT 2 and so on.
ops$tkyte%ORA11GR2> select null from dual group by cube( 'hello world', sysdate, 42 );
N
-
8 rows selected.