请教高手这样需求的SQL语句应该怎么写？

[zhangcm00]

有一个表table1,数据如下
A B C D
STERE 1 200 200
KG 2 400 400
UNIT 3 500 1500
INT 4 200 800
OUT 5 500 2500

我要得出的结果集是A字段中STERE和KG只能取一条，就是判断D字段谁的值大就取谁。A字段其他的值全部取，请问SQL语句应该怎么写？谢谢

[CT_LXL]

select * from table1 t1 where t1.D = (select max(t2.D) from table1 t2 where t2.A in ('STERE', 'KG')) AND T1.A IN ('STERE', 'KG') union all select * from table1 t3 where where t2.A not in ('STERE', 'KG');

[rick-he]

select * from table1 t1 where t1.D = (select max(t2.D) from table1 t2 where t2.A in ('STERE', 'KG') ) union all select * from table1 t3 where where t2.A not in ('STERE', 'KG') ;