Unanswered: Using MAX() with COUNT() and comparing
I'm currently leaning sql, and while waiting for my oracle account to be set up, I've been doddling about using mysql.
Retrieve the name and number of the project which uses the most employees. Also show the total number of employees for that project. If there is more than one project that has attained that maximum, list them all.
The problem is now displaying the row(s) that have the maximum employees. I've tried a handful of different techniques I found on forums...but nothing would work. Since I am new to this, I'm having a hard time knowing all the rules for subqueries, etc. I also found you can't do max(count(*)). Thanks for any help!
There are a number of ways to get the info you are after. You could go with your query and put an order by descending on your count column and only fetch the first record. You could use a sub-query that performs a count and join that to a nested expression to get the one with the highest count. Keep playing around with it and if you have problems, post them here for some tips on how to correct them.