MySQL: WHERE IN() limited to one row peer argument

Question

I've a MySQL table like this: id | reference_id | name

And I wish to get rows WHERE reference_id is IN a certain list of values: SELECT * FROM TABLE WHERE reference_id IN(2,3);

The problem is that the table will contain data like:

id | reference_id | name

1  | 1            | nameA
2  | 2            | nameB
3  | 2            | nameC
4  | 3            | nameD

And by running the query I'll get a result set like:

id | reference_id | name

2  | 2            | nameB
3  | 2            | nameC
4  | 3            | nameD

Is there a way I can tell MySQL to only return ONE value peer IN argument? Meaning, I would like a result set like:

id | reference_id | name

3  | 2            | nameC
4  | 3            | nameD

Answer 1

http://sqlfiddle.com/#!9/6412b/2

SELECT `table1`.* 
FROM `table1` 
LEFT JOIN `table1` t
ON `table1`.reference_id  = t.reference_id 
 AND `table1`.id<t.id
WHERE `table1`.reference_id IN(2,3)
  AND t.id IS NULL
GROUP BY `table1`.reference_id