如何让SQL查询仅返回每行对应的最大Ship_To_Zip值？

你的问题出在GROUP BY子句里包含了H.v3samzip——这会让每一个不同的邮编都单独成为一个分组，所以即使你用了max(H.v3samzip)，每个分组还是会返回对应的邮编，最终就出现了同一订单多条邮编记录的情况。

要实现每个订单只返回最大邮编的单行记录，只需要把H.v3samzip从GROUP BY里移除即可，修改后的SQL如下：

select
  d.V4ORD as Order_Number,  
  d.v4cust as Customer_Number,
  d.v4scusn as Customer_name, 
  d.v4itd3 as Equipment_ID,
  d.v4snam1 as SalesPerson,
  max(H.v3samzip) as Ship_To_Zip
from [].[HTSTRDP0] d  
left join [].[HTSTRHP0] H 
on D.V4INV=H.V3INVOICE 
  and d.v4ord=h.V3ORD 
  and d.v4cust=h.V3CUS and d.v4indt =h.V3INVDT
where d.V4DIV='3' 
  and left(d.v4item,3) in ('NTS','UTS','FTS') 
  and V4INDT>'20240101'
  and d.v4cust='YANK007' and d.v4itd3='014436-2001STOUGHT            '
group by 
  d.V4ORD , 
  d.v4cust,
  d.v4scusn, 
  d.v4itd3 ,
  d.v4snam1

修改后，GROUP BY只会按订单号、客户号等你需要聚合的字段分组，每个分组计算出最大的邮编，最终每个订单只会返回一条记录，就是包含最大v3samzip值的那一行。

修改后预期结果： #

内容的提问来源于stack exchange，提问作者tgruntzel