Finding duplicate value pairs in SQL

Question

I have a database that stores first and last names with a user id. The table looks like this:

uid value
1   Fred
1   Keller
2   Tim
2   LaChef
3   Adam
3   Adam

Having a duplicate uid is fine, but I want to find all entries that have the same first and last name though? Like uid 3. Any SQL ideas?

Why not run a query to check for duplicates, or are you asking something else? — samayo, Dec 20 '12 at 19:01
Please extend the sample data with 4 Adam 4 Sandler and post an expected result form the query. — Sir Rufo, Dec 20 '12 at 19:15
Your data does not have first and last names. Could you fix the data so it matches your problem? — Gordon Linoff, Dec 20 '12 at 20:50

Saharsh Shah · Accepted Answer · 2012-12-21T04:58:46.053

13

Try this:

SELECT uid FROM tablename 
GROUP BY uid, name HAVING COUNT(*) = 2;

edited Dec 21 '12 at 04:58

answered Dec 20 '12 at 19:04

Saharsh Shah

28,687
8
48
83

5

+1 but for more general i would have choosen COUNT(*) > 1 although it answers the question :o) – Sir Rufo Dec 20 '12 at 19:06
Yes but here we have 2 entries for each id so would prefer count(*)=2 – Saharsh Shah Dec 20 '12 at 19:08
Michel if you read question than question will say find all ids which have same firstname and lastname. It won't say name with different ids – Saharsh Shah Dec 20 '12 at 19:10
As i understand the question it says as i said earlier now we ask to adam that what he wants. – Saharsh Shah Dec 20 '12 at 19:15
beware that this may return null duplicates, which may or may not be desired – Mauricio Quintana Apr 30 '15 at 22:09

score 4 · Answer 2 · answered Dec 20 '12 at 19:33

4

To return just a single copy of each "duplicate", then:

SELECT t.uid
     , t.value
  FROM mytable t
 GROUP
    BY t.uid
     , t.value
HAVING COUNT(1) > 1
 ORDER
    BY t.uid
     , t.value

To return "all" entries that are duplicates, rather than just one copy, and if you don't need to return any NULL values, then:

SELECT a.uid
     , a.value
  FROM mytable a
  JOIN ( SELECT t.uid
              , t.value
           FROM mytable t
          GROUP
             BY t.uid
              , t.value
         HAVING COUNT(1) > 1
       ) d
     ON d.uid = a.uid
    AND d.value = a.value
  ORDER
     BY a.uid
      , a.value

If you do want to return NULL (where the NULL is a duplicate), then change the comparison operators in the ON clause to the null-safe equality comparison: <=>

     ON d.uid <=> a.uid
    AND d.value <=> a.value

answered Dec 20 '12 at 19:33

spencer7593

106,611
15
112
140

<=> produces an error on SQL SERVER 2012, nonetheless if ANSI_NULLS is ON, it won't considere NULL duplicates – Mauricio Quintana Apr 30 '15 at 22:18
@MauricioQuintana: Yes. The **`<=>`** null-safe comparison operator is a MySQL extension to the SQL standard. That operator isn't supported on SQL Server. (The question was tagged with "mysql", not "sql-server".) OP said he wanted to identify **"all entries that have the same first and last name"**. OP may want to consider a NULL "the same" as a NULL. Multiple rows with `NULL` values will be "grouped" together by a `GROUP BY`, and the individual rows do contribute to the count. The issue addressed by the **`<=>`** isn't about duplicates; it's about returning rows that have `NULL` values. – spencer7593 Apr 30 '15 at 23:59
@MauricioQuintana: The behavior we get in MySQL with **`a <=> b`** can be emulated in SQL Server by writing **`(a=b OR (a IS NULL AND b IS NULL))`**. – spencer7593 May 01 '15 at 00:02

Finding duplicate value pairs in SQL

2 Answers2