CODEWITHSUNDEEP
sqlmysql
Rana
Rana

Reputation: 6154

Help with SQL QUERY OF JOIN+COUNT+MAX

I need a help constructung an sql query for mysql database. 2 Table as follows:

tblcities (id,name)
tblmembers(id,name,city_id)

Now I want to retrieve the 'city' details that has maximum number of 'members'.

Regards

Upvotes: 0

Views: 835

Answers (4)

Marc B
Marc B

Reputation: 360772

SELECT tblcities.id, tblcities.name, COUNT(tblmembers.id) AS member_count
FROM tblcities
LEFT JOIN tblmembers ON tblcities.id = tblmembers.city_id
GROUP BY tblcities.id
ORDER BY member_count DESC
LIMIT 1

Basically: retrieve all cities and count how many members each has, sort by that member count in descending order, making the highest count first - then show only that first city.

Upvotes: 2

rsenna
rsenna

Reputation: 11973

Terrible, but that's a way of doing it:

SELECT * FROM tblcities WHERE id IN (
    SELECT city_id
    FROM tblMembers
    GROUP BY city_id
    HAVING COUNT(*) = (
        SELECT MAX(TOTAL)
        FROM (
            SELECT COUNT(*) AS TOTAL
            FROM tblMembers
            GROUP BY city_id
        ) AS AUX
    )
)

That way, if there is a tie, still you'll get all cities with the maximum number of members...

Upvotes: 1

John K.
John K.

Reputation: 5474

select top 1 c.id, c.name, count(*)   
from tblcities c, tblmembers m 
where c.id = m.city_id 
group by c.id, c.name 
order by count(*) desc

Upvotes: -1

Thomas
Thomas

Reputation: 64655

Select ...
From tblCities As C
    Join    (
            Select city_id, Count(*) As MemberCount
            From tblMembers
            Order By Count(*) Desc
            Limit 1
            ) As MostMembers
        On MostMembers.city_id = C.id

Upvotes: 0

Related Questions