> Is it possible to obtain by means of a consultation the minor number that does not exist in a status of numbers?
>
> For Example:
>
> Table.Id
> 1
> 2
> 4
> 5
> 8
> 9
> ....
>
> select a.id from table a where a.id between 1 and 1000000
>
> Desire than of as a Result = 3

Hi Ismael!

This would be quite easy to do in a stored procedure where you can have loops. A recursive CTE might seem an alternative, but I don't think recursion 1000000 levels deep will work. The closest to a solution using 'simple' SQL that I can think of would get you ranges of missing values and be something like:

SELECT MLow.Id+1 as MinFree, MHigh.Id-1 as MaxFree
FROM MyTable MLow
LEFT JOIN MyTable MHigh on MLow.Id < MHigh.Id
LEFT JOIN MyTable MNot on MNot.Id between MLow.Id+1 and MHigh.Id-1
WHERE MNot.Id IS NULL
AND MLow.Id < MHigh.Id - 1
AND MLow.Id BETWEEN 1 and 1000000
AND MHigh.Id BETWEEN 1 and 1000000
UNION
SELECT 1, MLowMin.Id-1
FROM MyTable MLowMin
LEFT JOIN MyTable MLowMinNot on MLowMinNot.Id < MLowMin.Id
and MLowMinNot.Id >= 1
WHERE MLowMinNot.Id IS NULL
AND MLowMin.Id > 1
UNION
SELECT MHighMax.Id+1, 1000000
FROM MyTable MHighMax
LEFT JOIN MyTable MHighMaxNot on MHighMaxNot.Id > MHighMax.Id
and MHighMaxNot.Id <= 1000000
WHERE MHighMaxNot.Id IS NULL
AND MHighMax.Id < 1000000
ORDER BY 1, 2

The unions are just to cover the possible cases where the first or last number your interested in doesn't exist (i.e. 1 or 1000000). If you know these exist, then you don't need any unions. With your dataset, I would expect the result to be:

MinFree MaxFree
3 3
6 7
10 1000000

If you prefer the result set to be:

MinFree MaxFree
3 <NULL>
6 7
10 1000000

then just use CASE:

SELECT MLow.Id+1 as MinFree, CASE WHEN MLow.Id < MHigh.Id-2 then MHigh.Id-1 ELSE NULL END as MaxFree

I haven't tried any of this SQL and may have done some stupid mistake, but still I hope this helps.

Set