declare @t table(time1 datetime,flag int,s1 varchar(10),s2 varchar(10)) insert into @t values('2009-01-04 08:16:29',1,'a ','a1') insert into @t values('2009-01-02 13:37:14',2,'3 ','53') insert into @t values('2009-01-03 13:01:02',1,'f3','12') insert into @t values('2009-01-01 13:01:02',2,'f3','12') insert into @t values('2009-01-02 13:01:02',3,'f3','12') insert into @t values('2009-01-03 13:01:02',2,'f3','12') select * from @T order by flag desc,time1 desc/* time1 flag s1 s2 ------------------------------------------------------ ----------- ---------- ---------- 2009-01-02 13:01:02.000 3 f3 12 2009-01-03 13:01:02.000 2 f3 12 2009-01-02 13:37:14.000 2 3 53 2009-01-01 13:01:02.000 2 f3 12 2009-01-04 08:16:29.000 1 a a1 2009-01-03 13:01:02.000 1 f3 12 */
create table #H ( time1 datetime, flag int, s1 nvarchar(20), s2 nvarchar(20) ) insert into #H select '2009-01-04 8:16:29', 1 , 'a','a1' union all select '2009-01-02 13:37:14', 2, '3', '53' union all select '2009-01-03 13:01:02', 1, 'f3', '12' union all select '2009-01-01 13:01:02', 2 , 'f3', '12' union all select '2009-01-02 13:01:02', 3, 'f3', '12' union all select '2009-01-03 13:01:02', 2, 'f3', '12' select time1,flag,s1,s2,row_number()over(partition by flag order by time1 desc) from #H order by flag desc
insert into @t values('2009-01-04 08:16:29',1,'a ','a1')
insert into @t values('2009-01-02 13:37:14',2,'3 ','53')
insert into @t values('2009-01-03 13:01:02',1,'f3','12')
insert into @t values('2009-01-01 13:01:02',2,'f3','12')
insert into @t values('2009-01-02 13:01:02',3,'f3','12')
insert into @t values('2009-01-03 13:01:02',2,'f3','12')
select * from @T order by flag desc,time1 desc/*
time1 flag s1 s2
------------------------------------------------------ ----------- ---------- ----------
2009-01-02 13:01:02.000 3 f3 12
2009-01-03 13:01:02.000 2 f3 12
2009-01-02 13:37:14.000 2 3 53
2009-01-01 13:01:02.000 2 f3 12
2009-01-04 08:16:29.000 1 a a1
2009-01-03 13:01:02.000 1 f3 12
*/
(
time1 datetime,
flag int,
s1 nvarchar(20),
s2 nvarchar(20)
)
insert into #H
select '2009-01-04 8:16:29', 1 , 'a','a1' union all
select '2009-01-02 13:37:14', 2, '3', '53' union all
select '2009-01-03 13:01:02', 1, 'f3', '12' union all
select '2009-01-01 13:01:02', 2 , 'f3', '12' union all
select '2009-01-02 13:01:02', 3, 'f3', '12' union all
select '2009-01-03 13:01:02', 2, 'f3', '12'
select time1,flag,s1,s2,row_number()over(partition by flag order by time1 desc)
from #H
order by flag desc