How can I list duplicate values out of a table?

Question

I have this table. Some of the rows have duplicate values in the Kanji column.

How can I show these rows where the same Kanji appears more than once?

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

score 7 · Accepted Answer · answered May 16 '17 at 12:12

7

You can use a GROUP BY statement by that column and specify a constraint that COUNT(*) of that group is larger than 1, so:

SELECT [kanji]
FROM [dbo].[Phrase]
GROUP BY [kanji]
HAVING COUNT(*) > 1

answered May 16 '17 at 12:12

Willem Van Onsem

443,496
30
428
555

score 3 · Answer 2 · answered May 16 '17 at 12:12

3

Group by with having will get which words are duplicates:

SELECT Kanji FROM Phrase
GROUP BY Kanji
HAVING COUNT(*)>1

answered May 16 '17 at 12:12

Arion

31,011
10
70
88

score 1 · Answer 3 · answered May 16 '17 at 12:12

1

select Kanji from MyTable
Group By Kanji
Having Count(*) > 1

I'd suggest having a full-text index on the column you want...

answered May 16 '17 at 12:12

Leonardo

10,737
10
62
155

score 0 · Answer 4 · answered May 16 '17 at 12:16

0

;with cteDuplicates
AS(
    SELECT *
        ,ROW_NUMBER()OVER (PARTITION BY Kanji ORDER BY Kanji) 'Dup'
    FROM dbo.Phrase
)
SELECT * FROM cteDuplicates D
WHERE D.Dup > 1

answered May 16 '17 at 12:16

Mazhar

3,797
1
12
29

1

Thank you for this code snippet, which may provide some immediate help. A proper explanation [would greatly improve](//meta.stackexchange.com/q/114762) its educational value by showing *why* this is a good solution to the problem, and would make it more useful to future readers with similar, but not identical, questions. Please [edit] your answer to add explanation, and give an indication of what limitations and assumptions apply. – Toby Speight May 16 '17 at 12:36

score 0 · Answer 5 · answered May 16 '17 at 12:18

0

SELECT COUNT(*) AS `doubles` FROM [table] GROUP BY `Kanji` HAVING `doubles` > 1;

answered May 16 '17 at 12:18

Remco K.

644
4
19

score 0 · Answer 6 · answered May 16 '17 at 12:21

It appears that he wants the duplicate rows, not just the values that are duplicated. One way to do this is to find the duplicate values in that column and then JOIN back to the original table to see the entire row. See the query below:

; WITH DuplicateKanji AS -- Query for duplicate values
(
    SELECT 
        Kanji 
    FROM Phrase
    GROUP BY Kanji
    HAVING COUNT(*)>1
)
SELECT -- Query to retrieve rows that were duplicates from above query
    p.*
FROM DuplicateKanji dk
    INNER JOIN Phrase p
    ON dk.Kanji = p.Kanji

How can I list duplicate values out of a table?

6 Answers6

Linked