SQL: Finding rows with duplicate fields, then select another field

Question

I have been searching for a solution to my issue but I can only find how to get duplicate rows in SQL that return the field the duplicate has matched on, but I can not find a solution where another row is selected.

I have the following SQL query:

SELECT * FROM Computer

Results (only relevant for this question):

ComputerID,ComputerCN
3,testmachine
5,testmachine

So far I have the following query:

SELECT COUNT(*), ComputerCN
  FROM Computer
  GROUP BY ComputerCN
  HAVING COUNT(*) > 1;

Results:

(no column name),ComputerCN
2, testmachine

So using the above query I know there are two rows where the field ComputerCN are duplicates. What I now need is not to return the ComputerCN, but to return the ComputerID's of the duplicate values. The result I desire is:

ComputerID
3
5

I have been trying to include ComputerID in the query like this:

SELECT COUNT(*), ComputerCN, ComputerID
  FROM Computer
  GROUP BY ComputerCN, ComputerID
  HAVING COUNT(*) > 1;

But I believe by doing this, it tries to find a match where both ComputerCN and ComputerID are duplicates, which is not the case.

How do I return a list of ComputerID's based on duplicate values from ComputerCN?

Answer 1

Use a subquery ,

SELECT ComputerID 
FROM Computer 
WHERE ComputerCN IN ( SELECT ComputerCN
                      FROM Computer
                      GROUP BY ComputerCN
                      HAVING COUNT(*) > 1
                     );

If Windows function are supported

WITH duplicates AS (
    SELECT *, 
           COUNT(*) OVER (PARTITION BY ComputerCN) dupes
    FROM Computer
) SELECT ComputerID
FROM duplicates
WHERE dupes > 1

See example