CODEWITHSUNDEEP
sqlselectduplicates
Salvo Danilo
Salvo Danilo

Reputation: 11

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

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         |         [email protected]       |
|          02         |         [email protected]        |
|          03         |         [email protected]       |
|          04         |         [email protected]      |
|          05         |         [email protected]        |
|          06         |         [email protected]     |

I want this

|          id         |        mail                |
|---------------------|----------------------------|
|          01         |  [email protected]       |
|          03         |  [email protected]       |

Upvotes: 1

Views: 112

Answers (4)

Joel Coehoorn
Joel Coehoorn

Reputation: 415790

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

Upvotes: 0

Mureinik
Mureinik

Reputation: 311338

If your 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

Upvotes: 1

Josh
Josh

Reputation: 1616

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 [email protected]

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

Upvotes: 0

Bryan Dellinger
Bryan Dellinger

Reputation: 5294

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)

Upvotes: 1

Related Questions