duplicate records multiple columns

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

Code MySQL:

---

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

---

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

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

InstID, DirID, RecordID

Code:

---

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

---

Code:

---

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

---

:cool:

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