Identify Identical values in 3 Consecutive records 1

[VickyC]

greetings

Consider the following table. The PK is (Set, Posit). Each Set has exactly 5 records as shown.

 [u] Set    Posit   X
[/u]
   1      1     4
   1      2     5
   1      3     5
   1      4     5
   1      5     2

   2      1     0    
   2      2     0    
   2      3     6    
   2      4     0    
   2      5     0
    
   3      1     2
   3      2     4
   3      3     4
   3      4     4
   3      5     4
   etc...

I want to identify which Sets have a run of 3 (or more) CONSECUTIVE IDENTICAL X values. In the example shown, I'd want to identify Set 1 (has three 5s in CONSECUTIVE Positions 2-4) and Set 3 (has three 4s in CONSECUTIVE positions 2-4, and again in position 3-5).


I currently can do this successfully, but I don't like the method. Here's what I do now...

a) I use a crosstab query to denormalize the table, like this...

[u]Set     X1   X2   X3   X4   X5
[/u]
 1       4    5    5    5    2
 2       0    0    6    0    0
 3       2    4    4    4    4 
 etc...

b) then I INSERT the above table into tbl_TEMP

c) then I delete the 'offending' Sets using SQL like...

   DELETE T.*
   FROM tbl_TEMP T
   WHERE ((T.X1=T.X2) AND (T.X2=T.X3) AND (T.X3=T.X4))
      OR ((T.X2=T.X3) AND (T.X3=T.X4) AND (T.X4=T.X5));

My QUESTION: How can I identify the Sets having 3 consecutive X values WITHOUT denormalizing, etc. I would like to be able to use the original normalized table.

thanks in advance for any help
Vicky

 

[PHV]

What about this ?

SELECT DISTINCT T1.Set
FROM (yourTable T1
INNER JOIN yourTable T2 ON T2.Set=T1.Set AND T2.Posit=T1.Posit+1 AND T2.X=T1.X)
INNER JOIN yourTable T3 ON T3.Set=T2.Set AND T3.Posit=T2.Posit+1 AND T3.X=T2.X

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[VickyC]

Thanks PHV - that works great!