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

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]       |

CodePudding user response：

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)

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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