Need help 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 identifiers in which there is no group in the sequence. So, for the example above, I only need 1000002 and 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

Since we do not need information about the number of the missing group, we can compare that the total number of elements is less or equal for a specific group

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

Answer 3

, :

SELECT
  id
FROM mytable m
GROUP BY id
HAVING (MAX(group) + 1) > COUNT(DISTINCT group);

( ). (ID, GROUP), DISTINCT.

Answer 4

, , 0, 1. :

SELECT id
FROM (
        SELECT  id, count(*) as cnt, max(group) as mx, min(group) as mn
        FROM    myTable
        GROUP BY group
     ) A
WHERE NOT mx = cnt - 1
AND NOT mn = 0

Hope this helps. This is probably not the cleanest or the most efficient, but hope this helps.

EDIT: Actually, reviewing the answer before mine and thinking about HAVING, it would probably be as clean as this.

SELECT ID
FROM   myTable
GROUP BY ID
HAVING MAX(group) >= COUNT(DISTINCT group)