We help IT Professionals succeed at work.

Locate first available spot?

potworny
potworny asked
on
Hi Experts,

Let's say we have a table consisting od the following numbers:

tbl_registration
1
2
3

5
6
7

How do I find the first available spot? :)

Best regards,
Terrible Monster
Comment
Watch Question

Data Warehouse / Database Architect
Commented:
Hi Pot,

Self join the table to itself, sliding one position.

SELECT Min (ID)
FROM
(
  SELECT t0.id, t1.id nextid
  FROM mytable t0
  LEFT JOIN mytable t1
    ON t0.id = t1.id - 1
) t2
WHERE nextid is NULL;


Good Luck,
Kent

Author

Commented:
Thank you very much :)