CODEWITHSUNDEEP
sqlgroup-byduplicates
user1772574
user1772574

Reputation: 68

Finding pairs of repeating entries

Could you please help me with one SQL query?

Table : Students

Id | Name | Date of birth
1    Will   1991-02-10
2    James  1981-01-20
3    Sam    1991-02-10

I need to find pairs of students who has same Date of birth. However, we are not allowed to use GROUP BY, so simply grouping and counting records is not a solution.

I have been trying to do it with JOIN, however with no success.

Your help is greatly appreciated!

Upvotes: 0

Views: 322

Answers (3)

wildplasser
wildplasser

Reputation: 44250

This query reports all students who have a non-unique birthdate.

SELECT *
FROM students s
WHERE EXISTS (
    SELECT *
    FROM students ex
    WHERE ex.dob = st.dob
    AND ex.name <> st.name
    )
ORDER BY dob
 ;

Upvotes: 0

user330315
user330315

Reputation:

You can use a self join on the table, joining on the date_of_birth column:

select s1.name,
       s2.name
from students s1
  join students s2 
    on s1.date_of_birth = s2.date_of_birth
   and s1.name < s2.name;

As wildplasser and dasblinkenlight pointed out the < operator (or >) is better than a <> because when using <> in the join condition, the combination Will/Sam will be reported twice.

Another way of removing duplicate those duplicates is to use a distinct query:

select distinct greatest(s1.name, s2.name), least(s1.name, s2.name)
from students s1
  join students s2 
    on s1.date_of_birth = s2.date_of_birth
   and s1.name <> s2.name;

(although eliminating the duplicates in the join condition is almost certainly more efficient)

Upvotes: 1

deviloper
deviloper

Reputation: 7240

select st.name, stu.name
from students st, students stu
where st.date_of_birth = stu.date_of_birth AND and st.name <> stu.name;

Upvotes: 0

Related Questions