create table #tb (id int,User1 nvarchar(100),ProductName nvarchar(100),AddTime smalldatetime)
---第一次:每一个用户按最新日期取一条排到前面
---第二次:其它产品直接按日期最新日期排!
insert into #tb
select 1, 'A' , 'AAB', '2006-1-1'
union all select 2, 'A' , 'BBD', '2006-1-4'
union all select 3, 'B' , 'CCA', '2006-9-8'
union all select 4, 'B' , 'DDD', '2006-8-5'
union all select 5, 'C' , 'EEf', '2006-9-5'
union all select 6, 'C' , 'fff', '2006-9-6'
select * from #tb a order by case when exists(select 1 from #tb where a.user1=user1 and a.addtime>addtime)
then 1 else 2 end ,addtime desc
結果:
3 B CCA 2006-09-08 00:00:00
6 C fff 2006-09-06 00:00:00
2 A BBD 2006-01-04 00:00:00
5 C EEf 2006-09-05 00:00:00
4 B DDD 2006-08-05 00:00:00
1 A AAB 2006-01-01 00:00:00
