--> 测试数据：[test]
if object_id('[test]') is not null drop table [test]
create table [test]([date] datetime,[state] int,[name] varchar(3))
insert [test]
select '2007-9-30',2,'aaa' union all
select '2007-9-30',1,'bbb' union all
select '2007-12-31',1,'ccc' union all
select '2007-12-31',2,'ddd' union all
select '2008-3-31',2,'eee' union all
select '2008-3-31',1,'fff' union all
select '2008-6-30',1,'ggg' union all
select '2008-6-30',2,'hhh' union all
select '2008-9-30',1,'iii' union all
select '2008-12-31',2,'kkk'

select * from [test] a
where a.state=(select MAX(state) from test b where a.date=b.date)
/*
date    state    name
2008-12-31 00:00:00.000    2    kkk
2008-09-30 00:00:00.000    1    iii
2008-06-30 00:00:00.000    2    hhh
2008-03-31 00:00:00.000    2    eee
2007-12-31 00:00:00.000    2    ddd
2007-09-30 00:00:00.000    2    aaa
*/

------解决方案--------------------
SQL code

create table za
(col1 date, col2 int, col3 varchar(5))

insert into za
select '2007-9-30', 2, 'aaa' union all
select '2007-9-30', 1, 'bbb' union all
select '2007-12-31', 1, 'ccc' union all
select '2007-12-31', 2, 'ddd' union all
select '2008-3-31', 2, 'eee' union all
select '2008-3-31', 1, 'fff' union all
select '2008-6-30', 1, 'ggg' union all
select '2008-6-30', 2, 'hhh' union all
select '2008-9-30', 1, 'iii' union all
select '2008-12-31', 2, 'kkk'


with t as
(select row_number() over(partition by col1 order by case col2 when 2 then 1 else 0 end desc) rn,
 col1,col2,col3 from za
)
select col1,col2,col3 from t
where rn=1

/*
col1       col2        col3
---------- ----------- -----
2007-09-30  2           aaa
2007-12-31  2           ddd
2008-03-31  2           eee
2008-06-30  2           hhh
2008-09-30  1           iii
2008-12-31  2           kkk

(6 row(s) affected)
*/

------解决方案--------------------
SQL code

select 
    CONVERT(varchar(10),[date],120) as [date],
    [state],
    [name]
from 
    test a
where 
    not exists(
        select 
            1 
        from 
            test b 
        where 
            a.[date]=b.[date] 
            and a.[state]<b.[state]
        )
--恩恩