SQL query max(), count()

Question

the database schema looks like

employee(employee_name,street,city)
works(employee_name,company_name,salary)
company(company_name,city)
manages(employee_name,manager_name)

the query needed to do is:
find the company that has the most employees.

I could find out the maximum count by the query:

SELECT max( cnt ) max_cnt
FROM (

SELECT count( employee_name ) cnt, company_name
FROM works
GROUP BY company_name
)w1;

But now I can't find out the name of the company. If anyone has some idea please share.

Answer 1

To get the entire row containing the maximum value you can use ORDER BY ... DESC LIMIT 1 instead of MAX:

SELECT company_name, cnt
FROM (
    SELECT company_name, count(employee_name) AS cnt
    FROM works
    GROUP BY company_name
) w1
ORDER BY cnt DESC
LIMIT 1