Find missing serials

m_jundi
m_jundi used Ask the Experts™
on
Hello,

How to find a missing sequence Using oracle 8i from a table ?  the purpose I need to assign available sequences automatically for customer accounts, as below example :

Account_no
1
2
4
5
8

results:
1) The result of the query is 3 , when (3) inserted in the table, query should get 6..etc.
2) Also how can I find all missing sequences , result : 3,6,7 using one select?

Thank You
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
Customer Accounts, Serial Numbers once assigned have an infinite lifetime. They never can be reused. If you delete them from the current database they should still exist in your data warehouse
Commented:
SELECT MIN(A.NUM-1) FROM TEST A WHERE (A.NUM-1)>0 AND
NOT EXISTS (SELECT 1 FROM TEST B WHERE A.NUM-1=B.NUM);

Author

Commented:
thank u
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
If your sample data has a 3, your query returns 7 not 6.

This one only hits the table once:
select min(rn) from (
	select num, row_number() over(order by num) rn from tab1
)
where num!=rn
;

Open in new window


To show all missing numbers, this is what I came up with:
with max_num as (
	select max(num) max_num from tab1
),
all_numbers as (
	select rownum rn from max_num connect by level <= max_num
),
combined as (
	select rn, num
	from all_numbers a left join tab1 t1 on a.rn = t1.num
)
select rn from combined where num is null order by rn
;

Open in new window


There are many other options on the web.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial