30

Suppose I have a SQL table "Celebrities" with two columns: "fname" and "lname":

fname | lname ---------+------- Bill | Clinton Bill | Gates George | Bush George | Clinton Barack | Obama

I would like to write a query that returns the first and last name of each person in the table whose last name appears at least twice in the column "lname". How do I write this SQL query?

Improve this question

3 Answers 3

Reset to default
70 
SELECT fname, lname FROM Celebrities WHERE lname IN (SELECT lname FROM Celebrities GROUP BY lname HAVING COUNT (lname) >1)
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks! I'm going to learn some new SQL syntax to fully grasp your answer, but it works!
12

Using a JOIN:

SELECT a.* FROM CELEBRITIES a JOIN (SELECT c.lname FROM CELEBRITIES c GROUP BY c.lname HAVING COUNT(*) >= 2) b ON b.lname = a.lname

Using EXISTS:

SELECT a.* FROM CELEBRITIES a WHERE EXISTS (SELECT NULL FROM CELEBRITIES c WHERE c.lname = a.lname GROUP BY c.lname HAVING COUNT(*) >= 2)
Improve this answer

1 Comment

A small performance note: I found the EXISTS method to be the fastest, then the JOIN method, and in a distant third the IN method. Also: having an index on the counted column (lname) is a big help too.
4 
select fname, lname from ( select fname, lname, count(*) over(partition by lname) as lcount from Celebrities ) as S where lcount > 1

Tested in SQL Server 2008. Might work in other DBMS that support count(*) over(...)

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.