I have a database table with 3 columns. I want to find all duplicates that have snuck in un-noticed and tidy them up.
Table is structured approximately
ID ColumnA ColumnB
0 aaa bbb
1 aaa ccc
2 aaa bbb
3 xxx bbb
So what would my query look like to return columns 0 and 2 as both column A and column B make a combined duplicate entry?
Standard sql preferred, but is running on a SQL 2008 server
You can create a query that groups and counts the duplicate rows:
SELECT COUNT(1) , ColumnA , ColumnB
FROM YourTable
GROUP BY ColumnA , ColumnB
HAVING COUNT(1) > 1
You can then add this to a subquery to output the full rows that hold the duplicate data.
Here's a full executable example based on your sample data:
CREATE TABLE #YourTable
([ID] INT, [ColumnA] VARCHAR(3), [ColumnB] VARCHAR(3))
;
INSERT INTO #YourTable
([ID], [ColumnA], [ColumnB])
VALUES
(0, 'aaa', 'bbb'),
(1, 'aaa', 'ccc'),
(2, 'aaa', 'bbb'),
(3, 'xxx', 'bbb')
;
SELECT *
FROM #YourTable t1
WHERE EXISTS ( SELECT COUNT(1) , ColumnA , ColumnB
FROM #YourTable
WHERE t1.ColumnA = ColumnA AND t1.ColumnB = ColumnB
GROUP BY ColumnA , ColumnB
HAVING COUNT(1) > 1 )
DROP TABLE #YourTable
Use count(*) as a window function:
select t.*
from (select t.*, count(*) over (partition by columna, columnb) as cnt
from table t
) t
where cnt > 1;
You may try like this:
with x as (select *,rn = row_number()
over(PARTITION BY columnA,columnB order by ID)
from #temp1)
select * from x where rn > 1
You can use a sub-select with a HAVING clause to find duplicated ColumnA-ColumnB pairs, then the outer SELECT just returns the matching rows.
select * from MyTable t1
inner join (select ColumnA, ColumnB
from MyTable
group by ColumnA, ColumnB
having count(*) > 1) t2 on t2.ColumnA = t1.ColumnA
and t2.ColumnB = t1.ColumnB
The code without aggregated functions:
SELECT
a.*
FROM
#tbl a
JOIN #tbl b ON a.[ColumnA] = b.[ColumnA]
AND a.[ColumnB] = b.[ColumnB]
AND a.id <> b.id
OR
SELECT
a.*
FROM
#tbl a
WHERE
EXISTS ( SELECT
*
FROM
#tbl b
WHERE
a.[ColumnA] = b.[ColumnA]
AND a.[ColumnB] = b.[ColumnB]
AND a.ID <> b.ID )
OR
SELECT * FROM (
SELECT
a.*, COUNT(*) OVER (PARTITION BY [ColumnA], [ColumnB]) cnt
FROM
#tbl a
) a
WHERE cnt > 1
This approach can be controversial and many people can claim its "a bad practice" but, it does perfectly translate "Pick all duplicate stuff from 'table'" Of course, it also works with delete statement.
SELECT FROM mytable WHERE Id NOT IN
(SELECT Id FROM
(SELECT Id, concat(ColumnA,'-',ColumnB) AS x FROM mytable
GROUP BY x) AS innerTable);
Also you could occasionally if possible (or necessary) add a unique index on those columns.
ALTER TABLE mytable
ADD CONSTRAINT uniqueColA_ColB UNIQUE (ColumnA,ColumnB);
And Sql will automatically throw an error when trying to insert duplicate values.
