How do I get the first free row?

[Darkwing99]

I have a table where COL1 is an increasing number starting with 1.
Rows can be deleted.

Now I need a SELECT-statement to get the first unused number within a given range.

Example:
COL1
1
2
3
4
6
8
12
13
14


I neet the first free number between 10 and 20.
The result has to be 10.

How can I select this value?

The best statement I explored is:

SELECT MIN(COL1) + 1                        
FROM TABLE_1
WHERE 
      COL1 > :LOW-RANGE - 1
  AND COL1 < :HIGH-RANGE - 1
  AND COL1 NOT IN                           
( SELECT COL - 1 FROM TABLE_1 )

But this SQL will bring the result 15 in the example, not 10.

Thanks for help.

Regards,
Thomas

 

[nigelrivett]

select COL1+1
from (select Col1 from tbl where Col1 between LOW-RANGE and HIGH-RANGE union select LOW-RANGE -1 union select HIGH-RANGE -1) as t1
and not exists (select * from tbl as t2 where t1.Col1+1 = t2.Col1)


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[nigelrivett]

That should be
between LOW-RANGE and HIGH-RANGE-1
I think

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[Darkwing99]

Thanx for the fast answer but it will not run.

select COL1+1
from (select Col1 from tbl 
       where Col1 between LOW-RANGE  and HIGH-RANGE 
      union

select LOW-RANGE -1

      union

select HIGH-RANGE -1

) as t1
where
   not exists 
   (select * from tbl as t2 
    where t1.Col1+1 = t2.Col1)

I use DB2v7 and the bold statements are not accepted.

I tried to change them into

   select LOW-RANGE -1 
   from SYSIBM.SYSDUMMY1

DB2 will do it, but I'm not allowed to use system-tabels. This part is blocked by our QM-Auditor.

Any other advice?

Regards,
Thomas

 

[nigelrivett]

Don't know db2 but can't you put the values into a temp table or something.
Or create a table with a single row.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[nigelrivett]

Is it the syntax for a variable it is complaining about?
If it needs a table there then

select COL1+1
from (select Col1 from tbl
where Col1 between LOW-RANGE and HIGH-RANGE
union
select min(id)/min(id) * LOW-RANGE -1 from tbl
union
select min(id)/min(id) * HIGH-RANGE -1 from tbl) as t1
where
not exists
(select * from tbl as t2
where t1.Col1+1 = t2.Col1)

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[r937]

"Or create a table with a single row"

that's a useful tip for every database except Oracle, which already has one (called DUAL)


for the gaps problem, perhaps a right outer join with an integers table?

how big are the gaps you will be testing for? what are the realistic min anx max IDs in the table, if not 1 and 14?

rudy

http://r937.com/

SQL Consulting

 

[Darkwing99]

The Tables have about 200 rows and the range is by default 50 to 100 but can be changed.

The One-Row-Table in DB2 is SYSIBM.SYSDUMMY1

Regards,
Thomas

 

[nigelrivett]

Probably create a tally table and join to that.
If it doesn't have the requisite number of values then add them before the join

something like

select min(tally.val) + LOW-RANGE
from tally
left outer join tbl
on tally.val + LOW-RANGE = tbl.Col1
where tally.val between 0 and HIGH-RANGE - LOW-RANGE
and tbl.Col1 is null


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[r937]

you could load an integers table with numbers from 1 to 200 but i prefer to generate as many integers as i need "on the fly" from only the first 10

create table integers (i integer);
insert into integers (i) values (0);
insert into integers (i) values (1);
insert into integers (i) values (2);
insert into integers (i) values (3);
insert into integers (i) values (4);
insert into integers (i) values (5);
insert into integers (i) values (6);
insert into integers (i) values (7);
insert into integers (i) values (8);
insert into integers (i) values (9);

select min(10*tens.i+ones.i)
from integers tens
cross
join integers ones
left outer
join yourtable
on 10*tens.i+ones.i = col1
where 10*tens.i+ones.i
between :LOW-RANGE and :HIGH-RANGE
and col1 is null

caution: untested

rudy

http://r937.com/

SQL Consulting

 

[nigelrivett]

I'd be a bit surprised if db2 doesn't have a means of creating a derived table from a literal though - most other databases do.



>>"Or create a table with a single row"
that's a useful tip for every database except Oracle, which already has one (called DUAL)

Umm - actually I think every database probably has this facility but most don't need it as it's covered by their sql implementation - I've given a suggestion on how it might be done in db2 but don't know whether it would work or not.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[Golom]

How 'bout

SELECT TOP 1 A.Num+1 AS Missing
FROM tbl AS A
WHERE NOT EXISTS (Select * From tbl As X Where X.Num = A.Num + 1)
ORDER BY A.Num;

 

[nigelrivett]

Golom
That's missing the range criteria which causes the problem.
See the original question and the problem with returning 10 not 15.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[Darkwing99]

Instead of filling or creating new tables I can make a second SELECT.

First I simply lokk for LOW-RANGE.
If LOW-RANGE is in the table I use the SELECT I postet first otherwise LOW-RANGE is my hit.

Thanks for all.

If anyone knows a simple 1-select-way post it. For now I will use the 2-select-solution.



Regards,
Thomas