Get id with most records in the table-CodePudding

Table name: Offices

CompanyID   OfficeLocation
01          USA
01          Africa  
03          USA
04          USA
04          Israel 
04          Germany
05          Africa

I need to get CompanyID that have most records in this table (04 for table above) I tried:

SELECT CompanyID
GROUP BY CompanyID
HAVING COUNT(*) = ( 
    SELECT COUNT (*) FROM EntitySite
    GROUP BY CompanyID
    ORDER BY COUNT(*) DESC
)

Dsnt work at all(

CodePudding user response：

You can use next simple query:

SELECT CompanyID, COUNT(OfficeLocation) CountOfficeLocation
FROM EntitySite
GROUP BY CompanyID
ORDER BY CountOfficeLocation DESC
LIMIT 1;

MySQL query online

CodePudding user response：

Firstly, question is abstract, data has no obvious relation (different id's have different countries), no know requirement what id is considered when counting (first, last or random).

However, here is query from what I get, this will return most common first and least common last.

SELECT CompanyID
FROM
(
    SELECT count(*) as c,
           CompanyID
    FROM Offices
    GROUP BY CompanyID
) T
ORDER BY c desc