Show All Duplicate Records QUERY-CodePudding

What is the query that will show all the records that have multiple names?

For example.

Table 1:

| ID | Name    | Age   | Email           |
 ---- --------- ------- ----------------- 
| 1  | Mike    | 12    | [email protected]  |
| 2  | Mon     | 10    | [email protected]   |
| 3  | Peter   | 12    | [email protected]   |
| 4  | Mike    | 13    | [email protected]|

Desired result:

| ID | Name    | Age   | Email           |
 ---- --------- ------- ----------------- 
| 1  | Mike    | 12    | [email protected]  |
| 4  | Mike    | 13    | [email protected]|

CodePudding user response：

One option, using COUNT() as an analytic function:

WITH cte AS (
    SELECT t.*, COUNT(*) OVER (PARTITION BY Name) cnt
    FROM yourTable t
)

SELECT ID, Name, Age, Email
FROM cte
WHERE cnt > 1;

CodePudding user response：

You can use GROUP BY and join result to origin table

SELECT t1.*
FROM myTable t1

JOIN

  (SELECT Name
   FROM myTable
   GROUP BY Name
   HAVING COUNT(Name) > 1) t2

ON t1.Name = t2.Name