我有表t1如下:
id code listdate sign
1 A00201101020001 2011-01-02 1
2 A00201101030002 2011-01-03 1
3 B00201101010001 2011-01-01 2
4 C00201102050003 2011-02-05 3
5 C00201101300004 2011-01-30 3
6 D00201101180003 2011-01-18 4
7 D00201101180004 2011-01-18 4我想实现,当相同sign时,取code最大的值,如上面数据取出如下:
id code listdate sign
2 A00201101030002 2011-01-03 1
3 B00201101010001 2011-01-01 2
4 C00201102050003 2011-02-05 3
7 D00201101180004 2011-01-18 4如何用sql语句来实现?
id code listdate sign
1 A00201101020001 2011-01-02 1
2 A00201101030002 2011-01-03 1
3 B00201101010001 2011-01-01 2
4 C00201102050003 2011-02-05 3
5 C00201101300004 2011-01-30 3
6 D00201101180003 2011-01-18 4
7 D00201101180004 2011-01-18 4我想实现,当相同sign时,取code最大的值,如上面数据取出如下:
id code listdate sign
2 A00201101030002 2011-01-03 1
3 B00201101010001 2011-01-01 2
4 C00201102050003 2011-02-05 3
7 D00201101180004 2011-01-18 4如何用sql语句来实现?
select id,code,listdate,sign
from (
select row_number() over(partition by sign order by code desc) as num,* from t1) aa
where num=1
declare @table table (id int,code varchar(15),listdate datetime,sign int)
insert into @table
select 1,'A00201101020001','2011-01-02',1 union all
select 2,'A00201101030002','2011-01-03',1 union all
select 3,'B00201101010001','2011-01-01',2 union all
select 4,'C00201102050003','2011-02-05',3 union all
select 5,'C00201101300004','2011-01-30',3 union all
select 6,'D00201101180003','2011-01-18',4 union all
select 7,'D00201101180004','2011-01-18',4select * from @table t
where code=(select max(code) from @table where sign=t.sign)
order by id
/*
id code listdate sign
----------- --------------- ----------------------- -----------
2 A00201101030002 2011-01-03 00:00:00.000 1
3 B00201101010001 2011-01-01 00:00:00.000 2
4 C00201102050003 2011-02-05 00:00:00.000 3
7 D00201101180004 2011-01-18 00:00:00.000 4
*/