query i had that does everything except "normalize" the zip
the MAX() In the sub query does nothing.. 

SELECT     b.ID, a.zip, b.Location, b.Move, b.Depot
FROM         (SELECT DISTINCT origNorm, MAX(zip) AS zip
                       FROM          dbo.BOA_Norm_addresses
                       WHERE      (zip > '') AND (zip IS NOT NULL)
                       GROUP BY origNorm, zip) AS a RIGHT OUTER JOIN
                      dbo.BOA_AAAAdepotFromTo AS b ON a.origNorm = b.Location
GROUP BY b.ID, a.zip, b.Location, b.Move, b.Depot
ORDER BY b.ID

Table a
origNorm | Zip
bnsf    70427
upRR    70471
upRR    70471-9852
upPier  08546
upPier  
upPier  08546-0844
RRcorp  23456
teset   92011


Table b
ID | Location | Move | Depot
1     bnsf      NULL    NULL       70427
2     upRR      NULL    NULL       70471
3     upRR      NULL    NULL       70471-9852
4     upPier    NULL    NULL       08546
5     upPier    NULL    NULL              
6     upPier    NULL    NULL       08546-0844
7     RRcorp    NULL    NULL       23456
8     teset     NULL    NULL       92011


Return This

ID | Location | Move | Depot   |  Zip
1     bnsf      NULL    NULL       70427
2     upRR      NULL    NULL       70471-9852
3     upRR      NULL    NULL       70471-9852
4     upPier    NULL    NULL       08546-0844
5     upPier    NULL    NULL       08546-0844      
6     upPier    NULL    NULL       08546-0844
7     RRcorp    NULL    NULL       23456
8     teset     NULL    NULL       92011

quote:

---

Originally posted by TG

remove [zip] from the GROUP BY of the sub-query. That is why max "does nothing".

Be One with the Optimizer
TG

---