duplicate records multiple columns

[digitalbart]

Hello,

I would like to find duplicate records based off two columns and then also get the unique ID / index field? Is this possible since I am doing a GROUP BY?

I want to delete duplicate records and it would be easier if I had the ID.

Thanks,
Bart

 
SELECT DirID, COUNT(*), RecordID, COUNT(*)
FROM Instance
GROUP BY DirID,RecordID
HAVING COUNT(*) > 1

[guido2004]

What is the table key? DirID/RecordID?
If so, you were almost there. Just eliminate the first COUNT(*) in your SELECT.

[digitalbart]

The Table Key is InstID, so there are 3 fields total:

InstID, DirID, RecordID

[swampBoogie]

select InstID,
    DirID,
    RecordID
  from instance
 where (DirID,RecordID) in
     (select DirID,recordID
        from Instance
       group by DirID,RecordID
      having count(*) > 1)

[r937]

SELECT Instance.InstID
     , Instance.DirID
     , Instance.RecordID
  FROM ( SELECT DirID
              , RecordID
           FROM Instance
         GROUP 
             BY DirID
              , RecordID
         HAVING COUNT(*) > 1 ) AS dupes
INNER
  JOIN Instance
    ON Instance.DirID    = dupes.DirID   
   AND Instance.RecordID = dupes.RecordID

[digitalbart]

Thank you swampboogie and r937. Both seemed to work! This is exactly what I needed. Thank you!