--> 测试数据：[SMT_cp]
if object_id('[SMT_cp]') is not null 
drop table [SMT_cp]
create table [SMT_cp](
[SMT_id] int,
[SMT_yp_id] int,
[SMT_data] datetime,
[SMT_cpname] varchar(6)
)
go
insert [SMT_cp]
select 1,9,'2012-4-12 14:33:36','饮料' union all
select 2,11,'2012-4-18 19:23:36','食品' union all
select 3,9,'2012-4-12 14:33:36','饮料' union all
select 4,9,'2012-4-12 14:33:36','香水' union all
select 5,12,'2012-5-19 19:23:36','饮料' union all
select 6,11,'2012-3-17 19:23:36','饮料' union all
select 7,12,'2012-5-19 19:23:36','饮料' union all
select 8,8,'2012-5-20 19:23:36','香油' union all
select 9,8,'2012-5-17 19:23:36','饮料' union all
select 10,8,'2012-5-18 19:23:36','饮料' union all
select 11,8,'2012-5-16 19:23:36','饮料' union all
select 12,10,'2012-5-19 19:23:36','口服液' union all
select 13,10,'2012-5-19 19:23:36','豆腐' union all
select 14,13,'2012-5-19 19:23:36','保健品' union all
select 15,13,'2012-6-19 16:23:41','饮料' union all
select 16,14,'2012-6-19 16:23:41','饮料'
go
--> 测试数据：[SMT_yp]
if object_id('[SMT_yp]') is not null 
drop table [SMT_yp]
create table [SMT_yp](
[SMT_id] int,
[SMT_coname] varchar(4)
)
go
insert [SMT_yp]
select 8,'王明' union all
select 9,'陈丽' union all
select 10,'海风' union all
select 11,'秋雨' union all
select 12,'夏天' union all
select 13,'毛毛' union all
select 14,'小黄'
go

;with t
as(
select 
    a.*,
    b.SMT_coname 
from 
    [SMT_cp] a
inner join 
    [SMT_yp] b
on 
    a.SMT_yp_id=b.SMT_id
where
    charindex('饮料',a.SMT_cpname)>0
)
select 
    *
from
    t a
where
    not exists(
        select 
            1
        from 
            t b
        where
            a.SMT_yp_id=b.SMT_yp_id
            and (
                (a.SMT_data<b.SMT_data) 
                or (a.SMT_data=b.SMT_data and a.SMT_id<b.SMT_id)
                )
     )
/*
SMT_id    SMT_yp_id    SMT_data    SMT_cpname    SMT_coname
------------------------------
10    8    2012-05-18 19:23:36.000    饮料    王明
3    9    2012-04-12 14:33:36.000    饮料    陈丽
6    11    2012-03-17 19:23:36.000    饮料    秋雨
7    12    2012-05-19 19:23:36.000    饮料    夏天
15    13    2012-06-19 16:23:41.000    饮料    毛毛
16    14    2012-06-19 16:23:41.000    饮料    小黄
*/

------解决方案--------------------

如果你的数据库是sql2000，用临时表试试
SQL code
select a.*,b.SMT_coname 
into #t
from [SMT_cp] a
inner join [SMT_yp] b
on  a.SMT_yp_id=b.SMT_id
where charindex('饮料',a.SMT_cpname)>0
go

select *
from #t a
where not exists(
  select 1 from #t b
  where a.SMT_yp_id=b.SMT_yp_id
  and ((a.SMT_data<b.SMT_data) or (a.SMT_data=b.SMT_data and a.SMT_id<b.SMT_id))
)