CODEWITHSUNDEEP
sqloracle10g
zSynopsis
zSynopsis

Reputation: 4854

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

Upvotes: 3

Views: 374

Answers (4)

Martijn
Martijn

Reputation: 5653

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.

Upvotes: 0

XstreamINsanity
XstreamINsanity

Reputation: 4296

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)

Upvotes: 0

Damian Leszczyński - Vash
Damian Leszczyński - Vash

Reputation: 30865

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

Upvotes: 2

Michael Pakhantsov
Michael Pakhantsov

Reputation: 25370

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

Upvotes: 2

Related Questions