Query for each City the ZipCode with the highest Total

Let's assume I have a table with the following fields:
City, ZipCode, Total
.. and the following data:
Roswell 32032 100
Roswell 32032 120
Roswell 34589 75
Atlanta 31902 345
Atlanta 35865 200
Atlanta 46321 110
Denver 31411 25
Denver 31411 50
I need a query that will return for each City the ZipCode with the highest
Total.
For example:
Roswell 32032
Atlanta 31902
Denver 31411Assuming that the city should be deterministic that should do the work:
Select * from
SomeTable s
Inner join
(
Select city,max(total) as Total
From SomeTable
Group by City
) Subquery
ON
Subquery.City = s.City AND
Subquery.Total = s.Total
THe Problem is that you didnt point out what is more important in that
case, the ZipCode or the city. One mor problem could be that there can be
duplicated. If you share some DDL with us, its always more easy to identify
your problem and help you solving it.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Scott" <someone@.microsoft.com> schrieb im Newsbeitrag
news:uPZkCD8XFHA.3876@.TK2MSFTNGP10.phx.gbl...
> Let's assume I have a table with the following fields:
> City, ZipCode, Total
> .. and the following data:
> Roswell 32032 100
> Roswell 32032 120
> Roswell 34589 75
> Atlanta 31902 345
> Atlanta 35865 200
> Atlanta 46321 110
> Denver 31411 25
> Denver 31411 50
> I need a query that will return for each City the ZipCode with the highest
> Total.
> For example:
> Roswell 32032
> Atlanta 31902
> Denver 31411
>
>|||Try,
select City, ZipCode, Total
from t1
where total = (select max(a.total) from t1 where a.city = t1.city)
-- or
select t1.City, t1.ZipCode, t1.Total
from t1 inner join (select city, max(total) as max_total from t1 group by
city) as t2
on t1.city = t2.city
AMB
"Scott" wrote:

> Let's assume I have a table with the following fields:
> City, ZipCode, Total
> ... and the following data:
> Roswell 32032 100
> Roswell 32032 120
> Roswell 34589 75
> Atlanta 31902 345
> Atlanta 35865 200
> Atlanta 46321 110
> Denver 31411 25
> Denver 31411 50
> I need a query that will return for each City the ZipCode with the highest
> Total.
> For example:
> Roswell 32032
> Atlanta 31902
> Denver 31411
>
>|||Correction,

> select t1.City, t1.ZipCode, t1.Total
> from t1 inner join (select city, max(total) as max_total from t1 group by
> city) as t2
> on t1.city = t2.city
select t1.City, t1.ZipCode, t1.Total
from t1 inner join (select city, max(total) as max_total from t1 group by
city) as t2 on t1.city = t2.city and t1.total = t2.max_total
AMB
"Alejandro Mesa" wrote:
> Try,
> select City, ZipCode, Total
> from t1
> where total = (select max(a.total) from t1 where a.city = t1.city)
> -- or
> select t1.City, t1.ZipCode, t1.Total
> from t1 inner join (select city, max(total) as max_total from t1 group by
> city) as t2
> on t1.city = t2.city
>
> AMB
> "Scott" wrote:
>