CODEWITHSUNDEEP
sql-serversequencenumeric
Christopher Klein
Christopher Klein

Reputation: 2793

how to find missing value in sequence within groups in SQL?

i have a table of IDs and positions

CREATE TABLE #MissingSequence (ID INT NOT NULL, Position INT NOT NULL)
INSERT INTO #MissingSequence (ID,Position)
SELECT 36,1
UNION ALL SELECT 36,2
UNION ALL SELECT 36,3
UNION ALL SELECT 36,4
UNION ALL SELECT 36,5
UNION ALL SELECT 36,6
UNION ALL SELECT 44,1
UNION ALL SELECT 44,3
UNION ALL SELECT 44,4
UNION ALL SELECT 44,5
UNION ALL SELECT 44,6

What I am trying to find is if there is any break in the sequence of Positions by ID in this case the break between 44,1 and 44,3

I've managed to parse together:

SELECT  l.ID
    ,Start_Position = MIN(l.Position) + 1
    ,Stop_Position = MIN(fr.Position) - 1
FROM #MissingSequence l
LEFT JOIN #MissingSequence r 
    ON l.Position = r.Position - 1
LEFT JOIN #MissingSequence fr 
    ON l.Position < fr.Position
WHERE r.Position IS NULL
    AND fr.Position IS NOT NULL
GROUP BY l.ID

but it doesn't work if there are multiple ID values. It does work if only a single ID, 44 exists.

thoughts, comments, suggestions?

thanks!

Upvotes: 3

Views: 5547

Answers (3)

user1900387
user1900387

Reputation: 51

create database testing
use testing;
create table sequence (
    id int not null primary key
);

insert into sequence(id) values
    (1), (2), (3), (4), (6), (7), (8), (9),
    (10), (15), (16), (17), (18), (19), (20);

select * from sequence

Create PROCEDURE test_proce(@mode varchar(50))   
AS
BEGIN
    declare @se int;
    set @se=0;
    set @se=(
        select top 1 t.id + 1 
        from sequence t 
        left join sequence x on x.id = t.id + 1 
        where x.id is null 
        order by t.id
    );
    select * from sequence where id<@se;
END

exec test_proce 'mode'

Upvotes: 0

bobince
bobince

Reputation: 536369

The left self-join was a good instinct, but I don't think the aggregates are going to cut it, and certainly you'd need to include the matching-ID clause in your self-joins.

Here's an (ANSI-compliant) version using the null-left-join idea, selecting a top row and a bottom row and checking there's nothing between them:

SELECT
    above.ID AS ID, below.Position+1 AS Start_Position, above.Position-1 AS End_Position
FROM MissingSequence AS above
JOIN MissingSequence AS below
    ON below.ID=above.ID AND below.Position<above.Position-1
LEFT JOIN MissingSequence AS inbetween
    ON inbetween.ID=below.ID AND inbetween.Position BETWEEN below.Position+1 AND above.Position-1
WHERE inbetween.ID IS NULL;

+----+----------------+--------------+
| ID | Start_Position | End_Position |
+----+----------------+--------------+
| 44 |              2 |            2 | 
+----+----------------+--------------+

Upvotes: 8

Jhonny D. Cano -Leftware-
Jhonny D. Cano -Leftware-

Reputation: 18013

This query spots the slips, hope to be useful; if you are in SQL 2005, you can use a CTE

SELECT ID, Position + 1
FROM #MissingSequence t1
WHERE (Position + 1) NOT IN (SELECT Position FROM #MissingSequence t2 WHERE t1.ID = t2.ID)
AND Position <> (SELECT MAX(Position) FROM #MissingSequence t2 WHERE t1.ID = t2.ID)

Upvotes: 2

Related Questions