sql query question
- by bu0489
hey guys, just having a bit of difficulty with a query, i'm trying to figure out how to show the most popular naturopath that has been visited in a centre. My tables look as follows;
Patient(patientId, name, gender, DoB, address, state,postcode, homePhone, businessPhone, maritalStatus, occupation, duration,unit, race, registrationDate , GPNo, NaturopathNo)
and
Naturopath (NaturopathNo, name, contactNo, officeStartTime, officeEndTime, emailAddress)
now to query this i've come up with
SELECT count(*), naturopathno FROM dbf10.patient WHERE naturopathno != 'NULL' GROUP BY naturopathno;
which results in;
COUNT(*) NATUROPATH
2 NP5
1 NP6
3 NP2
1 NP1
2 NP3
1 NP7
2 NP8
My question is, how would I go about selecting the highest count from this list, and printing that value with the naturopaths name? Any suggestions are very welcome,
Brad
