Another MySql prob, getting max of count...

Skud

Problem:

2 tables Staff and Branch
Share Bno as Identifier
Branch has address(more than one column)
Staff has a column called Sex, which determines whether male or female (obviously)

So problem is I need the Max number of female employees employed by a certain branch and the address of that branch. Problem is I tried using count(*) which gives it something like this

SELECT Bno,COUNT(*)
FROM staff
WHERE Sex = 'F'
GROUP BY Bno

But i can't get the data out for the highest number of female employees employed by the a certain branch.

I also need to take from the branch table (address) and dont know how to do this without adding on the total of branch and staff, instead of just staff. Can anyone help me or at least point me in the right direction? Cheers

amen

post both tables and some sample data with what you expect vs what you get

seamus

This is cheating slightly, but it should work.

SELECT staff.Bno, branch.address, COUNT(staff.*) as num_females
FROM staff, branch
WHERE staff.Sex = 'F' 
AND staff.Bno = branch.Bno
GROUP BY staff.Bno
ORDER BY num_females DESC
LIMIT 0, 1

Skud

that code works perfect, cheers man, helped alot.