How to select biggest zip code for each country

Question

In each country, which city has the highest ZIP code? Select only the Country name, and the city name

Here is a graphic schema, which may help you:

This is what I done so far:

SELECT CountryName, CityName
from City ci
join County co on co.CountryID = ci.CountryID
group by CountryName, CityName, ci.ZipCode
having ZipCode = MAX(ZipCode)

I would be grateful if someone can solve me this.

Answer 1

One option uses a lateral join:

select co.countryname, ci.cityname, ci.zipcode
from country co
cross apply (
    select top (1) with ties ci.* 
    from city ci 
    where ci.countryid = co.countryid
    order by ci.zipcode desc
) ci

You can also use row_number():

select co.countryname, ci.cityname, ci.zipcode
from country co
inner join (
    select ci.*, rank() over(partition by countryid order by zipcode desc) rn
    from city ci
) ci on ci.countryid = co.countryid

If you are running MS Access, you can use a correlated subquery:

select co.countryname, ci.cityname, ci.zipcode
from country co as co
inner join city as ci on ci.countryid = co.countryid
where ci.zipcode = (
    select max(c1.zipcode)
    from city as ci1
    where ci1.countryid = ci.country
)