If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
Returning Unique Fields based on MAX
"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) |
Thread Tools | |
Display Modes | |
|
|