Can't believe it takes me so long to do a so easy thing in sql

Modified at $Date: 2005/03/09 07:11:12 $

Can't believe it takes me so long to do a so easy thing in sql.

I have to do something to make myself the best. I forgot so much... Luckily I still can write something. ^_^.

A working guy beside me want me to help him to write a query. He want to select fields in a table which has one to many relation in the field in the same table. Suppose

ClinicAddress	Clinic
1	1
2	1
3	2
4	3
5	4
6	5
7	4

select field clinic which has more than one ClinicAddress. One clinic has more than one clinicAddress. How to do this in Query Analyzer.

sql query is below.

SELECT clinic, COUNT(*) as countNumber
FROM clinic_address 
GROUP BY clinic
HAVING COUNT(*) > 1
ORDER BY countNumber

Use HAVING clause to make it. Easy? But why I forgot it??? What a shame to me!