Dave Dave - 26 days ago 8
SQL Question

SQL for to find out of Sequence number

I am listing a example below. I have Id column with Seq number and I need to group them by id, and wherever Seq number is out of sequence.
BTW, I am using Teradata.

Data:

DATA Sample
id Seq
abcd 1
abcd 2
abcd 3
abcd 5
abcd 6
abcd 7
abcd 8
abcd 10
abcd 11
ab 2
ab 3
ab 4
ab 8
ab 9


Expected Result

id Seq Group_nbr
abcd 1 1
abcd 2 1
abcd 3 1
abcd 5 2
abcd 6 2
abcd 7 2
abcd 8 2
abcd 10 3
abcd 11 3
ab 2 1
ab 3 1
ab 4 1
ab 8 2
ab 9 2

Answer

You need to apply nested OLAP-functions:

SELECT id, Seq,
   Sum(Flag)  -- assign a group number
   Over (PARTITION BY id 
         ORDER BY Seq
         ROWS Unbounded Preceding) AS group_nbr
FROM
 (
   SELECT id, Seq,
      CASE WHEN Max(Seq)  -- indicate if there's a gap in the sequence
                Over (PARTITION BY id 
                      ORDER BY Seq
                      ROWS BETWEEN 1 Preceding AND 1 Preceding) + 1 = Seq
           THEN 0 
           ELSE 1
      END AS flag
   FROM tab
 ) AS dt