leo277 leo277 - 1 year ago 74
MySQL Question

SQL group by last name from a table with duplicate tuple

I have a question from an assignment for database using sqlite3 or MySQL. The question is:

Find all the last names belonging to two or more presidents. Do not repeat a last name, and remember that the same person serving two different terms (e.g., Grover Cleveland) does not constitute a case of two presidents with the same last name.

SELECT lastName FROM Presidents GROUP BY lastName HAVING COUNT(lastName) > 1;

I only managed to separate all the unique last names, however, I cannot remove same person that may be president twice such as Grover Cleveland.
Can someone teach me how I can remove the same person from the query?

I thought of including "WHERE firstName is UNIQUE", but it does not work.
Any help is appreciated. Thanks

Answer Source

You want to select last names whose count of distinct first names is greater than one. This query does precisely this:

SELECT lastName
FROM Presidents
GROUP BY lastName

Follow the link below for a running demo:


Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download