求一SQL语句

[mingyuebin]

我的一个表里有四个字段：
ID,barcode,createdate,path

其中，barcode的字段值是有可能会有重复的，其它三个字段是不会有重复的，现在我想做的是在select列表中需要同时选择这四个字段的值，但是barcode中如果有重复的话，只列出日期最新的记录，其它记录不显示

[hleb231]

select a.* from talbe a 
where a.barcode=(select max(createdate)from table)
order by id

[hleb231]

上面搞错了，不好意思

select a.* from talbe a 
where a.createdate=(select max(createdate)from table
where barcode=a.barcode)
order by id

[唐诗三百首]

select a.*
 from [表名] a
 where not exists(select 1 
                  from [表名] b
                  where b.barcode=a.barcode 
                  and b.createdate>a.createdate)

[xiaodongni]

select * from a where createtime=(select max(createtime) from a group by barcode )

[道素]

 SELECT tb1.* FROM tb1
 INNER JOIN (SELECT ID,barcode,PATH,MAX(createdate) AS createdate  from tb1 GROUP BY ID,barcode,path ) tb2
 ON tb1.ID=tb2.ID AND tb1.barcode=tb2.barcode AND tb1.path=tb2.path AND tb1.createdate=rb2.createdate

[MrYangkang]

if OBJECT_ID('tab1') is not null
drop table tab1
go
create table tab1
(
	id int primary key identity(1,1),
	barcode nvarchar(20),
	createdate date,
	path nvarchar(20)
)
insert into tab1
select 'barcode1','2014-08-05','无' union all
select 'barcode1','2014-08-10','无' union all
select 'barcode2','2014-08-07','无' union all
select 'barcode3','2014-08-09','无'
--1
select * from
(
select ROW_NUMBER() over( partition by barcode order by createdate desc) as d,* from tab1
)x
where d =1
--2
select a.* from tab1 a 
where not exists(select 1 from tab1 b where b.barcode=a.barcode and b.createdate>a.createdate)

[xiaodongni]

select * from a as a1 where createtime=(select max(createtime) from a as a2 where a1.barcode=a2.barcode )

[wtujedp]

create table t ( ID int, barcode varchar(20), createdate varchar(10), path varchar(100) ) insert into t values(1,'aaa','2011-01-01','a111111111111') insert into t values(2,'aaa','2012-01-01','a22222222222222') insert into t values(3,'bbb','2011-01-01','a3333333333333333333') insert into t values(4,'ccc','2011-01-01','a444444444444444') select t.* from t , (select barcode,MAX(createdate) createdate from t group by barcode )b where t.barcode=b.barcode and t.createdate=b.createdate order by id