need query to identify out-of-sequence counter

• ahmet erispaha

  Right there with Babe

  Points: 775

  More actions

  September 1, 2010 at 12:04 pm

  #225495

  CREATE TABLE demo(ref char(1),seq INT)

  INSERT INTO demo VALUES ('A',1)

  INSERT INTO demo VALUES ('A',2)

  INSERT INTO demo VALUES ('A',3)

  INSERT INTO demo VALUES ('B',1)

  INSERT INTO demo VALUES ('B',2)

  INSERT INTO demo VALUES ('B',3)

  INSERT INTO demo VALUES ('C',1)

  INSERT INTO demo VALUES ('C',1)

  INSERT INTO demo VALUES ('C',2)

  In the demo table, the seq values should be unique for each ref. Can someone help me come up with a query to identify any duplicates. For example in the sample data above, C 1 appears twice and should be identified so that we can investigate how it got there in the first place.

  Thanks,

• Cory E.

  SSCertifiable

  Points: 7076

  More actions

  September 1, 2010 at 12:13 pm

  #1215203

  SELECT ref

  , seq

  , COUNT(seq)

  FROM demo AS D

  GROUP BY ref

  , seq

  HAVING COUNT(seq) > 1

  -- Cory