SQL how to find rows that contain the same text in a column

user_6165260 0
Viewed 44

I want to extract the rows of a table that contain the same text in one column.

For example, I have this:

|          id         |        mail                       |
|---------------------|-----------------------------------|
|          01         |         dave.green@mail.com       |
|          02         |         joe.smith@mail.com        |
|          03         |         dave.green@mail.com       |
|          04         |         annie.white@mail.com      |
|          05         |         alice.inc@mail.com        |
|          06         |         ralph.lauren@mail.com     |

I want this

|          id         |        mail                |
|---------------------|----------------------------|
|          01         |  dave.green@mail.com       |
|          03         |  dave.green@mail.com       |
duplicates select sql
4 Answers

could you use an exists?

 select a.id, a.mail from my table a where exists
     (select 1 from mytable b where a.mail = b.mail and a.id <> b.id)
user_27447220

If your rdbms supports window function, using a window count may be the most elegant solution:

SELECT id, email 
FROM   (SELECT id, email, COUNT(*) OVER (PARTITION BY email) AS cnt
        FROM   mytable) t 
WHERE  cnt > 1
user_24227760

I expect there's a more-efficient way to do this using window functions, but this was the way that was fastest for me to write:

SELECT *
FROM [Table] t
INNER JOIN (
    SELECT t0.mail
    FROM [Table] t0
    GROUP BY t0.mail
    HAVING count(*) > 1
) g on g.mail = t.mail

I worked out the window function answer:

SELECT id, mail
FROM (
    select *, count(id) over (partition by mail) rows 
    from emails
) t
WHERE rows > 1
ORDER BY id

See it here:

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=36b7351b7895d34b5c6916e55c34ad13

Joel_Coehoorn_30430

Approach it in steps. First you want to find all the mails that occur more than once, right? You didn't say that, but that's what it feels like you want to do.

That would be this:

SELECT mail, count(1) as total_count
FROM table
GROUP BY mail
HAVING count(1) > 1

That should only return dave.green@mail.com

If that's correct, then all you need to do is join that result back to the table and get all the rows where that email matches. The easy way to do this is a join.

SELECT table.*
FROM table
INNER JOIN 
(
   SELECT mail, count(1) as total_count
   FROM table
   GROUP BY mail
   HAVING count(1) > 1
) as morethanonce
ON table.mail = morethanonce.mail
user_22120590
Related