求一条SQL语句的写法.(单表操作,类似于过滤数据)
有表
table1(col1   char(20),col2   int)
原始数据如下:
col1   col2
  a         1
  a         3
  a         13
  b         2
  c         123
  c         562
  ..
希望对于col1每一个值,只保留col2中最小的那一行,即最终得到如下:
  col1   col2
      a         1
      b         2
      c         123
-=====================
SQL2000中,我使用如下查询:
SELECT   *     FROM   TABLE1   AS   A
WHERE   EXISTS
(
  SELECT   *   FROM   TABLE1   AS   B
  WHERE   A.COL1   =   B.COL1   AND  
              A.COL2   >   B.COL2
)
可以看到返回的结果集就是我想删除的,但是把它改为
DELETE   FROM   TABLE1   ...(以后同上)
就出现了语法错误,恳请路过朋友代为解答
告之正确写法,谢谢~~~

------解决方案--------------------
select * from table1 as a
where not exists(select 1 from table1 where col1=a.col1 and col2 <a.col2)
------解决方案--------------------
select col1,min(col2) from table1 group by col1
------解决方案--------------------
select *
from table1 a
Where col2 in (Select Min(col2) From table1 Where col1 = a.col1)
------解决方案--------------------
学习
------解决方案--------------------
--try
delete (SELECT * FROM TABLE1 AS A
WHERE EXISTS
(
SELECT * FROM TABLE1 AS B
WHERE A.COL1 = B.COL1 AND
A.COL2 > B.COL2
)) a