求一个汇总的sql

[aspx_RICK]

Num Value Time
1 1.5 2009/05/01
1 3.5 2009/05/02
1 2.8 2009/05/03
1 1.5 2009/05/05
1 3 2009/05/06
2 2.1 2009/05/03
2 3.4 2009/05/04
2 3.5 2009/05/05
2 3.6 2009/05/06
2 2 2009/05/02
3 1.3 2009/05/03
3 1.9 2009/05/04
3 1.5 2009/05/05
3 1.3 2009/05/06
3 2.9 2009/05/04
5 3 2009/05/05
5 2.8 2009/05/06
5 2.9 2009/05/07
5 2.4 2009/05/08
6 2.3 2009/05/01
6 1.3 2009/05/02
6 3 2009/05/03
6 2.3 2009/05/04
6 3 2009/05/05
6 2.9 2009/05/10

我想要的结果是

1 3 2009/05/06
2 3.4 2009/05/04
5 3 2009/05/05
6 3 2009/05/03


1:按照Num分组
2:Time asc
3:Value大于3的第一条(如果没有大于3的值该Num就不要了)

[ws_hgo]

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Num] int,[Value] numeric(2,1),[Time] datetime)
insert [tb]
select 1,1.5,'2009/05/01' union all
select 1,3.5,'2009/05/02' union all
select 1,2.8,'2009/05/03' union all
select 1,1.5,'2009/05/05' union all
select 1,3,'2009/05/06' union all
select 2,2.1,'2009/05/03' union all
select 2,3.4,'2009/05/04' union all
select 2,3.5,'2009/05/05' union all
select 2,3.6,'2009/05/06' union all
select 2,2,'2009/05/02' union all
select 3,1.3,'2009/05/03' union all
select 3,1.9,'2009/05/04' union all
select 3,1.5,'2009/05/05' union all
select 3,1.3,'2009/05/06' union all
select 3,2.9,'2009/05/04' union all
select 5,3,'2009/05/05' union all
select 5,2.8,'2009/05/06' union all
select 5,2.9,'2009/05/07' union all
select 5,2.4,'2009/05/08' union all
select 6,2.3,'2009/05/01' union all
select 6,1.3,'2009/05/02' union all
select 6,3,'2009/05/03' union all
select 6,2.3,'2009/05/04' union all
select 6,3,'2009/05/05' union all
select 6,2.9,'2009/05/10'


select t1.[Num],t1.Value,min(t1.Time) Time from 
(
 select *,
     (select count(*) from [tb] where Num=t.Num and Value>t.Value) rank
 from [tb] t where value >= 3
)
 t1
join
(
select Num,max(rank) rank from
(
 select *,
     (select count(*) from [tb] where Num=t.Num and Value>t.Value) rank
 from [tb] t where value >= 3
) tt
group by Num
) t2
on t1.Num=t2.Num and t1.rank=t2.rank
group by  t1.[Num],t1.Value
order by t1.[Num]

Num         Value                                   Time
----------- --------------------------------------- -----------------------
1           3.0                                     2009-05-06 00:00:00.000
2           3.4                                     2009-05-04 00:00:00.000
5           3.0                                     2009-05-05 00:00:00.000
6           3.0                                     2009-05-03 00:00:00.000

(4 行受影响)

[ws_hgo]

马甲吗》

http://topic.csdn.net/u/20100313/12/c4cef202-d55e-48f2-8b3e-38d0a7343769.html?50365