Query help need in finding missing numbers

Question

I have a table that looks something like this:

ID         | GROUP
-------------------
1000001    | 0
1000001    | 1
1000001    | 2
1000002    | 0
1000002    | 2
1000002    | 3
1000003    | 1
1000003    | 2
1000003    | 3
1000004    | 0

I need to list all the ids where there is a group missing in the sequence. So for the above example i would only need back 1000002 & 1000003.

Thanks in advance

Answer 1

select distinct id
from
(
    SELECT Id, Group, LAG(Group, 1, -1) over (partition by Id order by Group) prevGroup
 FROM Table
 )     
 WHERe Group -1 <> PrevGroup

Answer 2

As we don need the information about the missing group number we can compare that over all count of elements is lower or equal for specific group

SELECT ID FROM YOUR_TABLE
GROUP BY ID 
HAVING COUNT(ID) <= max(GROUP);