排除重复行记录

[willjacky33]

select d.CompanyName, b.BizInfoType, b.Title, b.price,b.PublishTime
from YSBiz10_Fct_BizInfo as b
inner join YSBiz10_Fct_User as d on b.UserID=d.UserID

inner join YSBiz10_Rel_UserFavorites as t on b.UserID=t.FavoriteUserID
where t.UserID='3678c81f-fbe4b83-90c4-e27aa01d1571' and t.FavoriteUserID=b.UserID


查出结果集:

CompanyName Title price

a 铝合金 100
b 钢铁 300
b 钢 300
要修改为:

CompanyName Title price

a 铝合金 100
b 钢铁 300

也就是排除CompanyName重复的行
怎么改

[cbgn]

b 钢铁 300
b 钢 300
不重复呀，ＬＺ写错了？还是要解析字符串？

[喜-喜]

;with t as(select d.CompanyName, b.BizInfoType, b.Title, b.price,b.PublishTime  
from YSBiz10_Fct_BizInfo as b  
inner join YSBiz10_Fct_User as d on b.UserID=d.UserID
inner join YSBiz10_Rel_UserFavorites as t on b.UserID=t.FavoriteUserID  
where t.UserID='3678c81f-fbe4b83-90c4-e27aa01d1571' and t.FavoriteUserID=b.UserID )
select * from t tt where not exists(select 1 from t where CompanyName=t.CompanyName and Title<t.Title)

[cap77]

http://topic.csdn.net/u/20080626/00/43d0d10c-28f1-418d-a05b-663880da278a.html?96478

[willjacky33]

表 ：(省略几个字段)
CompanyName time

a 2010/8/1
a 2010/8/9
d 2010/8/3

语句:
with data as
(
select ROW_NUMBER() over (order by b.UserID) as ID, d.CompanyName, b.BizInfoType, b.Title, b.price,b.PublishTime from YSBiz10_Fct_BizInfo as b
inner join YSBiz10_Fct_User as d on b.UserID=d.UserID
inner join YSBiz10_Rel_UserFavorites as t on b.UserID=t.FavoriteUserID
where t.UserID='3678c81f-fbe4b83-90c4-e27aa01d1571' and t.FavoriteUserID=b.UserID
)
select * from data where ID in (select MIN(ID) from data group by CompanyName )
结果集:
a 2010/8/1
d 2010/8/3
可是我想得到时间（time）最近的不重复的记录:
a 2010/8/9
d 2010/8/3
请问怎么改

[喜-喜]

use test
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
CompanyName char(2),
time datetime
)
go
--插入测试数据
insert into tb select 'a','2010/8/1'
union all select 'a','2010/8/9'
union all select 'd','2010/8/3'
go
--代码实现

select * from tb t where not exists(select 1 from tb where CompanyName=t.CompanyName and time>t.time)

/*测试结果

CompanyName time
----------------------------------
a 	2010-08-09 00:00:00.000
d 	2010-08-03 00:00:00.000

(2 行受影响)
*/

select CompanyName,time=convert(varchar(10),time,111) from tb t where not exists(select 1 from tb where CompanyName=t.CompanyName and time>t.time)

/*测试结果

CompanyName time
-----------------------
a 	2010/08/09
d 	2010/08/03

(2 行受影响)
*/