Returning Unique Fields based on MAX

I am not all that familiar with writing SQL and am hoping that you'll be able
to help!

I have a table containing sales territories and 3 digit zipcodes. Because of
an overlap in the data, I am not getting a unique list of zipcodes, but
rather a list that may look like this:

Zip Territory
012 Boston
012 Hartford



I would like to return a unique list of zipcodes, with the associated
territories based on which territory has the max amount of revenue.

Currently, my SQL looks like this:

SELECT Base.[3DIGITZIP], Max(Base.Volume) AS MaxOfVolume, Base.SALES_TER2
FROM Base
GROUP BY Base.[3DIGITZIP], Base.SALES_TER2
ORDER BY Base.[3DIGITZIP];


Any help would be appreciated!

"tessaco" u53691@uwe wrote in message news:99dbd4393ab7b@uwe...
I am not all that familiar with writing SQL and am hoping that you'll be
able
to help!

I have a table containing sales territories and 3 digit zipcodes. Because
of
an overlap in the data, I am not getting a unique list of zipcodes, but
rather a list that may look like this:

Zip Territory
012 Boston
012 Hartford



I would like to return a unique list of zipcodes, with the associated
territories based on which territory has the max amount of revenue.

Currently, my SQL looks like this:

SELECT Base.[3DIGITZIP], Max(Base.Volume) AS MaxOfVolume, Base.SALES_TER2
FROM Base
GROUP BY Base.[3DIGITZIP], Base.SALES_TER2
ORDER BY Base.[3DIGITZIP];


You could do it with a subquery, probably something like this:

SELECT B.[3DIGITZIP], B.SALES_TER2, B.Volume
FROM Base As B
WHERE B.Volume =
(
SELECT Max(T.Volume) FROM Base As T
WHERE T.[3DIGITZIP] = B.[3DIGITZIP]
)

Note that this will return multiple records for the same [3DIGITZIP] if and
only if two or more territories for that zip have the same Volume. If that
isn't acceptable, the SQL will have to be modified to implement a
tie-breaker.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)