SQL - How to return all column fields for one column containing duplicates

Question

I'm trying to get to grips with SQL as I'm implementing SQLite into my existing program.

I'm trying to work out how to display the results of a search for duplicate values in one particular column, and where duplicates are found, I want ALL the rows that correspond to the column cell where a duplicate was found, to be displayed.

For example, for data in the database is :

Column A | Column B | Column C
Peter    | Smith    | 1234
Claire   | Walter   | 1234
Sandra   | Kay      | 0000
Oliver   | Meeson   | 3456
Alex     | Grey     | 0000
James    | Garnder  | 2489

I want to search Column C for duplicates. So, the query would find that rows 1 and 2 contain duplicated values in Column C, as well as rows 3 and 5. So, where found, I need it to return all fields for all three columns where column C contains duplicates. So the result I want is this:

Peter    | Smith    | 1234
Claire   | Walter   | 1234
Sandra   | Kay      | 0000
Alex     | Grey     | 0000

I read several posts like this and this and this last one which was the most helpful in that by using SELECT DISTINCT it did return only the UNIQUE values (and therefore excluded duplicate ones), but that is visa versa to what I want, and it did not also return the Columns A and B with the results. The suggestions in the earlier two posts just keep returning empty results when I know there are duplicate values in the database.

Hoping someone could help?

score 3 · Accepted Answer · answered Sep 21 '17 at 14:03

3

You can have query which first gets all duplicate values based on that particular column and retrieve records which is in that duplicate values.

Your query should be like this :

SELECT Column_A, Column_B, Column_C
FROM your_table 
WHERE Column_C IN (
    SELECT Column_C
    FROM your_table
    GROUP BY Column_C
    HAVING COUNT(*) > 1)

answered Sep 21 '17 at 14:03

Akash KC

16,057
6
39
59

Thank you so much! That is perfect and worked exactly as I needed first time. I spent hours trying to find the answer, you did so within minutes. Very much appreciated, thank you. – Gizmo_the_Great Sep 21 '17 at 17:04

Mohamed Chaawa · Answer 2 · 2017-09-21T16:33:10.277

0

You may use the GROUP BY statement like this:

SELECT A, B, C
FROM T 
WHERE C IN (SELECT C
            FROM T
            GROUP BY C
            HAVING COUNT(*) > 1)

edited Sep 21 '17 at 16:33

answered Sep 21 '17 at 14:03

Mohamed Chaawa

918
1
9
23

This does not return all rows. – CL. Sep 21 '17 at 16:24
@CL. Can you explain further? – Mohamed Chaawa Sep 21 '17 at 16:25
Sorry, I misread your GROUP BY clause. But this query does not search for duplicates only in column `C`. – CL. Sep 21 '17 at 16:26
@CL. You are right. I just fixed my answer to fit in this. – Mohamed Chaawa Sep 21 '17 at 16:33

SQL - How to return all column fields for one column containing duplicates

2 Answers2