Mysql select N of X duplicates, omitting 1 duplicate

Question

Considering this table:

+-----+--------+
| id  | value  |
+-----+--------+
|  1  |   22   |
+-----+--------+
|  2  |   12   |
+-----+--------+
|  3  |   22   |
+-----+--------+
|  4  |   22   |
+-----+ -------+

I can select all where the column value is duplicated like so:

select value from table having count(value) > 1 ;

This will output the Ids 1,3 and 4.

What I'm attempting to do is select where duplicates, but leaving 1 (one) duplicate un selected, so the above would output only the Ids 3 and 4 (or 1 and 3 etc... the duplicate omitted does not matter, only that it is.

How can I achieve this?

This question IS NOT a duplicate of

Using LIMIT within GROUP BY to get N results per group?

score 0 · Answer 1 · answered Jun 05 '17 at 16:55

You could use an aggregatio function for filter a value for id and the select all the others

  select * from table 
  where (value, id) not in (
      select value, max(id) 
      from table 
      group by value 
      having count(value) > 1 
  )
  ;

score 0 · Answer 2 · answered Jun 05 '17 at 17:02

You can do either as:

select * 
 from test t1
where exists (select 1 
                from test t2 
               where t2.value = t1.value 
              having count(value)>1)
limit 2

OR:

select t1.* 
  from test t1 inner join
       (select value from test t2 having count(value)>1) t2
       on t1.value = t2.value
 limit 2;

Mysql select N of X duplicates, omitting 1 duplicate

2 Answers2