关于sql语句的写法

表#t1数据如下：
SQL code

f1        listdate        remark
c          2011-12-12       
a          2011-12-02       123
b          2011-11-01       44rr4
c          2011-12-01       774
c          2011-12-09       oiiie2
d          2011-12-2        
a          2011-11-02       234

我想取当listdate最大的日期，取remark的数据，并且remark不能为空
SQL code

f1        listdate        remark
a          2011-12-02       123
b          2011-11-01       44rr4
c          2011-12-09       oiiie2

注意：不能取f1=c、listdate=2011-12-12的数据，因为remark为空的。
如何写sql语句？  

SQL code

select * 
from #t1 a 
where remark is not null --如果不为空串,则 remark<>'' 下同
and not exists(select 1 from #t1 where f1=a.f1 and remark is not null and listdate>a.listdate)

SQL code

select
 * 
from
 #t1 t 
where
 remark is not null 
and
 not exists(select 1 from #t1 where f1=t.f1 and remark is not null and listdate>t.listdate)

+1

SQL code

create table #t1
(f1 char(3),
listdate date,
remark varchar(9))

insert into #t1
select 'c', '2011-12-12', '' union all       
select 'a', '2011-12-02', '123' union all
select 'b', '2011-11-01', '44rr4' union all
select 'c', '2011-12-01', '774' union all
select 'c', '2011-12-09', 'oiiie2' union all
select 'd', '2011-12-2', '' union all       
select 'a', '2011-11-02', '234'


select a.* 
from #t1 a
inner join
(select f1,max(listdate) maxlistdate
from #t1 where remark<>'' group by f1) b
on a.f1=b.f1 and a.listdate=b.maxlistdate

f1   listdate   remark
---- ---------- ---------
a    2011-12-02 123
b    2011-11-01 44rr4
c    2011-12-09 oiiie2

(3 row(s) affected)

mark

select top 1 * from tb where remark is not null order by listdate desc

select top 1 * from tb where remark is not null order by listdate desc

select t.* from tb t where remark is not null and listdate = (select max(listdate) from tb where f1 = t.f1 and remark is not null)

select t.* from tb t where remark is not null and not exists (select 1 from tb where f1 = t.f1 and remark is not null and listdate > t.listdate)

SQL code

if object_id('tb') is not null
   drop table tb
go
create table tb
(
 f1 varchar(10),
 listdate varchar(10),
 remark varchar(10)
)
go
insert into tb
select 'c','2011-12-12','' union all
select 'a','2011-12-02','123' union all
select 'b','2011-11-01','44rr4' union all
select 'c','2011-12-01','774' union all
select 'c','2011-12-09','oiiie2' union all
select 'd','2011-12-2','' union all
select 'a','2011-11-02','234'
go
select * from tb a where remark<>'' and not exists(select 1 from tb where f1=a.f1 and listdate>a.listdate and remark<>'')
go
/*
f1         listdate   remark
---------- ---------- ----------
a          2011-12-02 123
b          2011-11-01 44rr4
c          2011-12-09 oiiie2

(3 行受影响)
*/