Maybe it is about missing IDs, and the solution is one of row generator techniques. For example, let's create a table and insert some records in there:
Code:
SQL> create table test (id number);
Table created.
SQL> insert all
2 into test values (1)
3 into test values (4)
4 into test values (5)
5 into test values (8)
6 into test values (10)
7 select * from dual;
5 rows created.
SQL>
It is obvious that IDs that are missing are 2, 3, 6, 7 and 9. OK then, let's find them!
First, using a CONNECT BY clause, create the whole ID set:
Code:
SQL> select level id
2 from dual
3 connect by level <= (select max(id) from test);
ID
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
One more step to a solution - using a MINUS set operator, find missing IDs:
Code:
SQL> select level missing_id
2 from dual
3 connect by level <= (select max(id) from test)
4 minus
5 select id
6 from test;
MISSING_ID
----------
2
3
6
7
9
That would be all, I guess.
dBforums
>
Challenging query
11-24-11, 03:56
Linear Mode
