Select duplicate using partition by in MySQL?

Question

I would like to select all duplicate from a table:

SELECT * FROM people HAVING (count(*) OVER (PARTITION BY name)) > 1;

Unfortunately I get the error:

Error Code: 3593. You cannot use the window function 'count' in this context.

One less elegant solution would be:

SELECT 
    *
FROM
    people
WHERE
    code IN (SELECT 
            name
        FROM
            people
        GROUP BY name
        HAVING COUNT(*) > 1);

How can I rewrite my first query to make it work?

https://stackoverflow.com/questions/2594829/finding-duplicate-values-in-a-sql-table — BlackSwan, Apr 17 '19 at 15:13

score 1 · Accepted Answer · answered Apr 17 '19 at 15:09

1

If the code is identical then you can use exists :

select p.*
from people p 
where exists (select 1 from people p1 where p1.name = p.name and p.code <> p1.code);

Use identity column instead if code column doesn't have a identity feature if entire table has no any identity column then your method works fine with following updated query :

SELECT p.*
FROM people p
WHERE name IN (SELECT name FROM people GROUP BY name HAVING COUNT(*) > 1);

answered Apr 17 '19 at 15:09

Yogesh Sharma

49,870
5
26
52

Well, I would like to use `PARTITION BY` as requested in the question – nowox Apr 17 '19 at 15:14
And, thus, avoid using two `SELECT` queries – nowox Apr 17 '19 at 15:15

Select duplicate using partition by in MySQL?

1 Answers1