If you don't mind watching your computer work hard, you can do something like this:

SELECT inc.i, m.LowKey + inc.i Incrementor, s3.hKey
FROM
(
SELECT (SELECT COUNT(*) i FROM PR_Paystub s2 WHERE s2.hKey < s.hKey)
FROM PR_Paystub s
ORDER BY s.hKey
) inc
CROSS JOIN (SELECT MIN(hKey) LowKey FROM PR_Paystub) m
LEFT OUTER JOIN PR_Paystub s3 ON s3.hKey = m.LowKey + inc.i

I tested this on a table in my project and it produces a result, but I only have 4,000 records in my table. A stored procedure/execute block solution is much more scalable.

--- In firebird-support@yahoogroups.com, "Svein Erling" <svein.erling.tysvaer@...> wrote:
>
> > 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
>