Query help need in finding missing numbers

Question

Hi all,

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

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.

Answer 2

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 3

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)

Answer 4

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