CODEWITHSUNDEEP
sqlcompare
Neutrino
Neutrino

Reputation: 781

Compare rows in SQL

I have got a sample table like this.

ID           Name
1            Jane  
2            John
3            Kevin
4            George
5            Jane

The result I would like to get from the query would be a table of two values: 

ID           Name
1            Jane
5            Jane

I know this could be done using 2 copies of a table and then comparing names of one and the other table but I couldn't get this working, though.

--

Sorry, didn't add this. I want to get all duplicate values.

I can see all the responses about duplicates and so on, I've seen the post on stackoverflow about finding duplicates using count(*) but I wonder whether it could be done by creating two copies of the table (say t and tt) and then checking t.name = tt.name?

Upvotes: 1

Views: 3157

Answers (4)

Albert Laure
Albert Laure

Reputation: 1722

I usually recommend using Joins rather than exist,in or not in in the codes. 

 Select A.ID,B.Name from Tablename A
    inner join Tablename B
    on A.Name = B.Name
     where a.id <> b.id

and

I don't usually recommend this, ID's should normally be joined rather than the name. here is the DEMO

Upvotes: 3

Esteban Elverdin
Esteban Elverdin

Reputation: 3582

Try this

SELECT ID, Name
FROM table1
WHERE Name IN
 (SELECT Name
  FROM table1
  GROUP BY Name
  HAVING COUNT(ID) > 1
 )
ORDER BY ID

Upvotes: 0

Teja
Teja

Reputation: 13534

SELECT A.*
FROM SAMPLE_TABLE A,
(
SELECT NAME,COUNT(*)
FROM SAMPLE_TABLE
GROUP BY NAME
HAVING COUNT(*) > 1
) B
WHERE A.NAME = B.NAME;

Upvotes: -1

John Woo
John Woo

Reputation: 263723

One way of doing this is by using EXISTS().

SELECT  *
FROM    tablename a
WHERE   EXISTS
        (
            SELECT  1
            FROM    tableName b
            WHERE   a.Name = b.name
            GROUP   BY Name
            HAVING  COUNT(*) > 1
        )

Upvotes: 3

Related Questions