table employee id bigint PK not null employee_name varvhar(100) not null employee_number int4 not null id; employee_name; employee_number 1000; Ivanov; 123 WITH ordered AS ( SELECT employee_number, LAG(employee_number) OVER (ORDER BY employee_number) as prev_number FROM employee ) SELECT prev_number + 1 as missing_number FROM ordered WHERE employee_number - prev_number > 1; 1000; name1; 1 1001; name2; 2 1002; name3; 4 1003; name4; 5 with ordered as () select * from ordered

In your example, you want to find missing employee numbers in the employee_number sequence.

Using the LAG window function allows you to get the previous employee number for each row, ordered by employee_number. Then, you select cases where the difference between the current and previous number is greater than 1, indicating a gap.

Example query:

WITH ordered AS (
    SELECT employee_number,
           LAG(employee_number) OVER (ORDER BY employee_number) AS prev_number
    FROM employee
)
SELECT prev_number + 1 AS missing_number
FROM ordered
WHERE employee_number - prev_number > 1;

If you have data:

id	employee_name	employee_number
1000	name1	1
1001	name2	2
1002	name3	4
1003	name4	5

This query will return 3, since the number 3 is missing between 2 and 4.

To find all missing numbers (for example, if multiple are missing in a row), you can extend the query, for example, using generate_series, but this depends on your DBMS.

Example for PostgreSQL with generate_series:

SELECT missing_number
FROM (
  SELECT generate_series(
    MIN(employee_number),
    MAX(employee_number)
  ) AS missing_number
  FROM employee
) AS all_numbers
LEFT JOIN employee e ON e.employee_number = all_numbers.missing_number
WHERE e.employee_number IS NULL;

This query will return all missing numbers within the range.