How can I list duplicate rows in a table with the same two columns?

Question

I have this table:

CREATE TABLE [dbo].[Phrase] (
    [PhraseId]              UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
    [English]               NVARCHAR (250)   NOT NULL,
    [Kanji]                 NVARCHAR (250)   NULL,
    PRIMARY KEY CLUSTERED ([PhraseId] ASC) );

What I would like to do is to get a list of all the duplicate rows (with English and Kanji) columns used to detect a duplicate.

In other words if there are rows like this:

English   Kanji

aa        bb
aa        cc
aa        bb
aa        dd
aa        ee
aa        dd

I would like to get a report showing

aa        bb
aa        dd

Possible duplicate of [Finding duplicate values in a SQL table](http://stackoverflow.com/questions/2594829/finding-duplicate-values-in-a-sql-table) — Mikhail Lobanov, May 16 '17 at 14:17
This question has been answered many times. You already asked it too: http://stackoverflow.com/questions/44001073/how-can-i-list-duplicate-values-out-of-a-table — Mikhail Lobanov, May 16 '17 at 14:18

score 2 · Accepted Answer · edited May 16 '17 at 14:22

2

try this:

select English,Kanji from Phrase
group by English,Kanji
having count(*)>1

edited May 16 '17 at 14:22

Eli

2,538
1
25
36

answered May 16 '17 at 14:02

Dgan

10,077
1
29
51

score 0 · Answer 2 · answered May 16 '17 at 14:57

Using Row_Number() we can do it

;WITH CTE(English,Kanji)
as
(
select 'aa','bb' UNION ALL
select 'aa','cc' UNION ALL
select 'aa','bb' UNION ALL
select 'aa','dd' UNION ALL
select 'aa','ee' UNION ALL
select 'aa','dd' 
)
SELECT English
    ,Kanji
FROM (
    SELECT *
        ,ROW_NUMBER() OVER (
            PARTITION BY English
            ,Kanji ORDER BY English
                ,Kanji
            ) AS Seq
    FROM CTE
    ) dt
WHERE dt.Seq <> 1

OutPut

English Kanji
-------------
aa      bb
aa      dd

How can I list duplicate rows in a table with the same two columns?

2 Answers2