You need to use group by to get the initial data and then you can put all of that into a sub-query. The following example is in T-SQL and was tested against SQL Server as I am unable to load MySQL at this time. The syntax may have some minor errors but the principal works:
I created a table called
kals84
with your data as per the question and this query returns the values you require:
select q.cnt, q.mx, k.name, q.city
from kals84 k
inner join (select city, count(*) as cnt, max(id) as mx
from kals84 group by city) q on k.city = q.city and q.mx = k.id
The key to this working is the sub-query
select city, count(*) as cnt, max(id) as mx
from kals84 group by city
which returns the values
city cnt mx
city1 3 4
city2 2 5
You get the
name
from joining to those results based on the
city
and the derived
mx
column