关于批量排序取值的讨论

[david_xu322]

现有表A
create table a (id int,name varchar(50),num float)

insert into a(1,'a',0.1)
insert into a(2,'a',0.13)
insert into a(3,'a',0.06)
insert into a(4,'a',0.3)
insert into a(5,'a',0.58)
insert into a(6,'a',0.36)
....
insert into a(7,'b',0.11)
insert into a(8,'b',0.35)
insert into a(9,'b',0.16)
insert into a(10,'b',0.32)
insert into a(11,'b',0.78)
insert into a(12,'b',0.26)
....

要得到的结果
按name分组，得到排序的95%值
例如a
select top 1 @value = tmp_value from (select top 5 percent num as tmp_value from a where name='a' order by name desc) b order by name
现在要用一条语句获得a,b,....所有的对应的95%值，排除循环获取95%值，不知还有没有其它的方法，欢迎大家讨论！

[david_xu322]

楼上的方法好象不行也，我要得到的是第95个值

想要的结果
a,0.58
b,0.78

[bill024]

select * from a where id in
(
select id=cast(id*0.95 as int) from
(
select id=max(id) from a group by name
)a
)

[david_xu322]

可能大家没理解我的意思，将表A中name='a'的记录升序排，取第95%个的值，select top 1 tmp_value from (select top 5 percent num as tmp_value from a where name='a' order by name desc) b order by name
同理获得name='b','c',.....的95%值

[子陌红尘]

select
t.*
from
a t
where
t.num in (select top 95 percent num from a where name=t.name order by num desc)
order by
t.name,t.num