Query help need in finding missing numbers

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 lis开发者_运维问答t 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

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

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);

What I'm getting from your comments is that the ID must start with a group of 0 and the group should always increment only by 1. From that, I get this:

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. It's probably not the cleanest or most effective, but hope it will help.

EDIT: Actually, after re-looking over the answer before mine and thinking about HAVING, it probably would be cleaner like this.

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

Maybe this:

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

(I've written the keys in lowercase). If you have a unique key on (ID, GROUP), you can leave out the DISTINCT.