Select rows which appear more than given number of times in table

Question

I have table in mysql db from which I need to select thoose rows, which are duplicated more than given number of times, but result should not be merged (if that row was five times in table, I want it to appear five times in result)

Table has three columns: Id, Name, Surname

I have tried grouping like this:

SELECT t.Name, t.Surname FROM table t WHERE t.Id IN ( SELECT tt.Id FROM table tt GROUP BY tt.Name, tt.Surname HAVING count(*) > 10 )

I know where is my mistake - in this subquery, because as I understand it returns only one id per unique Name + Surname, but I don't know how to achieve desired behavior properly.

Mureinik · Accepted Answer · 2014-12-14 21:41:59Z

One way to go about this would be to join your original table with the aggregate query:

SELECT t.* FROM t JOIN (SELECT name, surname, COUNT(*) FROM t GROUP BY name, surname HAVING COUNT(*) > 10) aggr ON t.name = aggr.name AND t.surname = aggr.surname

Collectives™ on Stack Overflow

Select rows which appear more than given number of times in table

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related