Filter duplicates from mysql result depending on column value

I'm having some problems working out how to return the desired rows from the following mysql table:

first_name      last_name      collection
==========================================
   Bob            Jones            1
   Ted            Jones            1
   Bob            Jones            1
   Bob            Jones            2
   Ted            Baker            2

I want to return the count of names based on columns 'first_name' and 'last_name'. Ordinarily it would just be a simple case of using 'group by first_name, last_name', so 开发者_如何学编程we would have the following result: a count of 3 for Bob Jones, 1 for Ted Jones, and 1 for Ted Baker.

However, the difficulty is the third column 'collection'. I need to exclude duplicate names between collections, but not within collections. So we'd include all names in the count for the first collection, but only include names in the count from the second collection if they do NOT occur in the first collection.

So, the desired result would be as follows: a count of 2 for Bob Jones, 1 for Ted Jones, and 1 for Ted Baker.

first_name      last_name      collection             included?
==========================================         ===============
   Bob            Jones            1                    Yes
   Ted            Jones            1                    Yes
   Bob            Jones            1                    Yes
   Bob            Jones            2                    No
   Ted            Baker            2                    Yes

I have really tried to get my head around this but I am starting to run out of ideas. Any help would be hugely appreciated... thanks!

Perhaps

SELECT first_name, last_name, COUNT( DISTINCT collection) AS cnt
FROM yourtable
GROUP BY first_name, last_name

The COUNT DISTINCT would eliminate the duplicate Bob/Jones/1 records.

Is this what you are expecting?

SELECT u1.first_name, u1.last_name, u1.collection FROM users u1 
WHERE u1.collection = (SELECT min(u2.collection) 
    FROM users u2 WHERE u1.first_name = u2.first_name
    AND u1.last_name = u2.last_name);

http://www.sqlfiddle.com/#!2/bd086/7