iTunes SQL Oct31 '04

# (1 of 5): Michael Dale » dalegroup.net

1 hour, 10 minutes after the fact. (Sun 31 Oct 2004, 5:13 PM CST)

SELECT first_name, last_name, count(first_name) AS count, count(last_name) AS count2
FROM persons_table
GROUP BY last_name having count > 1 AND count2 > 2

DISTINCT will just pull out the value once. So if I had two Matts I would only see one.

# (2 of 5): Dale » dalegroup.net

1 hour, 10 minutes after the fact. (Sun 31 Oct 2004, 5:14 PM CST)

opps. Change

count2 > 2

to

count2 > 1

Previous comment Return to entry

# (3 of 5): Matthom

6 hours, 5 minutes after the fact. (Sun 31 Oct 2004, 10:08 PM CST)

Excellent. Thanks.

Previous comment Return to entry

# (4 of 5): Scott

9 months after the fact. (Thu 04 Aug 2005, 8:22 AM CST)

It's actually simpler than that:

select count(*), first, last
from yourtable
group by first, last
having count(*) > 1

Previous comment Return to entry

# (5 of 5): Sloot

3 years, 7 months after the fact. (Mon 16 Jun 2008, 10:16 PM CST)

slight improvement to previous comments

select * from personstable where (firstname, lastname) in ( select firstname, lastname from personstable group by firstname, lastname having count(*) > 1 ) order by firstname, lastname

this gives you ALL the columns from the persons_table, not just the first & last names

Previous comment Return to entry