Succesive Duplicates

[Leighton21]

Hi all,

I am trying to extend the following query to find successive duplicates

SELECT Value,
COUNT(Value) AS Duplicates
FROM TABLE1
GROUP BY Value
HAVING ( COUNT(Value) > 1 )

So I have a table that has the follwing data

Date ID Value
12/01/2008 1 12
12/02/2008 2 22
12/03/2008 3 15
12/04/2008 4 15
12/05/2008 5 15
12/06/2008 6 21
12/07/2008 7 18
12/08/2008 8 19
12/09/2008 9 15
12/10/2008 10 4

If I use the above query it will detect that 15 has 4 duplcates but what I am trying to do is find successive duplicates so it will only find the records on the 3rd, 4th and 5th of December. I am using SQL 2005 and thought of joining the table on itself (with the previous record) to see where it changes but I think that this may not function the way I think.

Cheers

 

[tlbroadbent]

Here is one possible solution that avoids the self-join of TABLE1. The key is determining if a record exists with the same value on day before or after the date in the current record. This code assumes that the Date column is a DATETIME data type.

SELECT [Value], COUNT(*) AS Duplicates
FROM TABLE1 t1
WHERE EXISTS (SELECT * FROM TABLE1 WHERE [Value] = t1.[value] AND ([date] = t1.[date] - 1 OR [date] = t1.[date] + 1))
GROUP BY Value
HAVING (COUNT(*) > 1)

Terry L. Broadbent - DBA

"The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents. (Nathaniel Borenstein)