I have to write a query to get a result set from table T
where table T is defined as
I need to get the rows that have the same value in column As and also have same value in column Cs. How to write the query? (using generic SQL query)
Use:
SELECT a.PrimaryKey, b.PrimaryKey
FROM T a
INNER JOIN T b
ON a.columnA = b.columnA
AND a.columnC = b.columnC
AND a.PrimaryKey < b.PrimaryKey
This will give all couples of rows (only one time with the inequality clause).
If that is too much (having three pairs A–B, A–C, B–C) it is also possible with standard SQL to restrict to the case where the left key is the minimal key for the group (you will then get only A–B and A–C):
SELECT a.PrimaryKey, b.PrimaryKey
FROM T a
INNER JOIN T b
ON b.columnA = a.columnA
AND b.columnC = a.columnC
LEFT JOIN T c
ON c.columnA = a.columnA
AND c.columnC = a.columnC
AND c.PrimaryKey < a.PrimaryKey
WHERE a.PrimaryKey < b.PrimaryKey
AND c.PrimaryKey IS NULL
To find the tuples A,C that have duplicate in table you can use
SELECT A, C, count(*)
FROM T
GROUP BY A, C
HAVING count(*) >=2
Now you can select all rows from table T that have A, C in this "duplicates" set.
Select PrimaryKey, A, B, C
FROM T JOIN
(SELECT A, C, count(*)
FROM T
GROUP BY A, C
HAVING count(*) >=2
) dupl
on T.A = dupl.A and T.C = dupl.C
