Max Query

[Guest]

I have created a table for the following three columns:-

City(Code) Country(code) Score (Value/number)

In this table the city names are unique but the countys can be duplicated

Is it possible to write a query in access which displays the cities with the
highest score for each country.

Thanks

 

[Guest]

hi,

yes... Just use...

SELECT tbl_name.city, Max(tbl_name.score) AS MaxOfscore
FROM tbl_name
GROUP BY tbl_name.city;

Hipe this helps,
geebee

 

[John Spencer]

What about ties in the score? This reports all cities in a country if they
share a high score.

Step 1: qryMaxScore (saved query)
Select Country, Max(Score) as BigScore
FROM YourTable
GROUP BY Country

Step 2: Use the saved query above along with your table
SELECT T.Country, T.City, T.Score
FROM YourTable as T INNER JOIN qryMaxScore as Q
ON T.Country = Q.Country and T.Score = Q.BigScore

All in one (may or may not work depending on your field and table names)
SELECT T.Country, T.City, T.Score
FROM YourTable as T INNER JOIN
(SELECT Country, Max(Score) as BigScore
FROM YourTable
GROUP BY Country) as Q
ON T.Country = Q.Country and T.Score = Q.BigScore

Post back if you need instructions on how to do this using the query grid.