How to find missing no in a sequence [message #7173] |
Mon, 26 May 2003 08:36 |
Behzad Khan
Messages: 1 Registered: May 2003
|
Junior Member |
|
|
How to find missing no in a sequence?
e.g
if i have table student how to write a query which could show the missing no for our case in rage of 1 to 4,3 is missing.
Student
============
ID Name
1 Tom
2 Sarah
4 Ram
|
|
|
|
Re: How to find missing no in a sequence [message #7175 is a reply to message #7173] |
Mon, 26 May 2003 09:44 |
jigar
Messages: 74 Registered: July 2002
|
Member |
|
|
Try this...
Create Table a (a Number);
Insert into a values (1);
Insert into a values (2);
Insert into a values (4);
Commit;
declare cursor cur is select * from a order by a;
b number;
c boolean;
begin
for i in cur
loop
if c=true then
if i.a <> b+1 then
dbms_output.put_line(b+1);
end if;
else
c:=true;
end if;
b := i.a;
end loop;
end ;
/
|
|
|