I have this little script that shall return the first number in a column of type int which is not used yet.

SELECT t1.plu   1 AS plu
FROM tovary t1
WHERE NOT EXISTS (SELECT 1 FROM tovary t2 WHERE t2.plu = t1.plu   1)
  AND t1.plu > 0;

this returns the unused numbers like

1. 3
2. 11
3. 22
4. 27 ...

The problem is, that when I make a simple select like

SELECT plu
FROM tovary
WHERE plu > 0
ORDER BY plu ASC;

the results are

1. 1
2. 2
3. 10
4. 20 ...

Why the first script isn't returning some of free numbers like 4, 5, 6 and so on?

CodePudding user response：

Compiling a formal answer from the comments.

Credit to Larnu:

It seems what the OP really needs here is an (inline) Numbers/Tally (table) which they can then use a NOT EXISTS against their table.

Sample data

create table tovary
(
  plu int
);

insert into tovary (plu) values
(1),
(2),
(10),
(20);

Solution

Isolating the tally table in a common table expression First1000 to produce the numbers 1 to 1000. The amount of generated numbers can be scaled up as needed.

with First1000(n) as
(
    select row_number() over(order by (select null))
    from       ( values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0) ) a(n) -- 10^1
    cross join ( values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0) ) b(n) -- 10^2
    cross join ( values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0) ) c(n) -- 10^3
)
select top 20 f.n as Missing
from First1000 f
where not exists (  select 'x'
                    from tovary
                    where plu = f.n);

Using top 20 in the query above to limit the output. This gives:

Missing
-------
3
4
5
6
7
8
9
11
12
13
14
15
16
17
18
19
21
22
23
24