select C_ID,C_Category,E_Amount,E_Category,P_A0,P_A1 ,(CASE WHEN P_A2<0 THEN 0 ELSE P_A2 END)as P_A2 from(select *,ROW_NUMBER() over(PARTITION by C_ID order by P_A1 desc) rownum from @temp)t where rownum=1
C_ID 是分组的，
P_A1 是排序的
原文：https://www.cnblogs.com/shuaimeng/p/14721156.html