Mysql select N of X duplicates

Asked Jun 05 '17 at 16:19

Active Jun 05 '17 at 16:30

Viewed 21 times

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?

EDIT: I'm not sure why this is marked as a duplicate, as I am asking how to omit a duplicated result, not how to limit or rank.

edited Jun 05 '17 at 16:30

asked Jun 05 '17 at 16:19

Kisaragi

2,198
3
16
28

Doesn't matter which one is left unselected? – Alfabravo Jun 05 '17 at 16:21
@Alfabravo: Doesn't matter, as long as it has a duplicated value. – Kisaragi Jun 05 '17 at 16:22
The fact that "it doesn't matter" is often indicative of poor design. It *should* matter!!! – Strawberry Jun 05 '17 at 16:25
Wow, this is definitively not a duplicate of that question... – Alfabravo Jun 05 '17 at 16:34

Mysql select N of X duplicates

0 Answers0