如何取分组的TOPN记录

[hnwzhb]

CI AssessedValueUP AssessedValueDW
99991 45.973 43.262
99991 47.862 43.262
99991 62.436 74.352
99991 47.098 55.342
99991 80.94 57.972
99992 53.856 62.436
99992 44.663 45.576
99992 52.055 41.401
99992 229.23 36.768
99992 36.768 53.856

如上面数据，我想取按CI分组，AssessedValueUP与AssessedValueDW排序的TOP3，如何实现，谢谢

[guguda2008]

SELECT *
FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY CT ORDER BY AssessedValueUP,AssessedValueDW) AS NID,*
FROM TB
) T WHERE NID<=3

[heymal]

select * from tb a
where exists
(select top 3* from tb b where b.CI=a.CI order by AssessedValueUP, AssessedValueDW)

[dawugui]

--sql 2000
select CI ,AssessedValueUP, AssessedValueDW from
(
  select t.* , px=(select count(1) from tb where CI = t.CI and (AssessedValueUP < t.AssessedValueUP or (AssessedValueUP = t.AssessedValueUP and AssessedValueDW < t.AssessedValueDW)) ) + 1 from tb t
) m
where px <= 3

--sql 2005
select CI ,AssessedValueUP, AssessedValueDW from
(
  select t.* , px=row_number() over(partition by CI order by AssessedValueUP, AssessedValueDW) from tb t
) m
where px <= 3

[abuying]

select * from tb a
where exists
   (select top 3* from tb b where b.CI=a.CI and a.AssessedValueUP=b.AssessedValueUP and a.AssessedValueDW and b.AssessedValueDW order by AssessedValueUP, AssessedValueDW)

[dawugui]

create table tb(CI varchar(10),AssessedValueUP decimal(18,2),AssessedValueDW decimal(18,2))
insert into tb values('99991', 45.973 ,43.262)
insert into tb values('99991', 47.862 ,43.262)
insert into tb values('99991', 62.436 ,74.352)
insert into tb values('99991', 47.098 ,55.342)
insert into tb values('99991', 80.94  ,57.972)
insert into tb values('99992', 53.856 ,62.436)
insert into tb values('99992', 44.663 ,45.576)
insert into tb values('99992', 52.055 ,41.401)
insert into tb values('99992', 229.23 ,36.768)
insert into tb values('99992', 36.768 ,53.856)
go

--sql 2000
select CI ,AssessedValueUP, AssessedValueDW from
(
  select t.* , px=(select count(1) from tb where CI = t.CI and (AssessedValueUP < t.AssessedValueUP or (AssessedValueUP = t.AssessedValueUP and AssessedValueDW < t.AssessedValueDW)) ) + 1 from tb t
) m
where px <= 3


drop table tb

/*

CI         AssessedValueUP      AssessedValueDW      
---------- -------------------- -------------------- 
99991      45.97                43.26
99991      47.86                43.26
99991      47.10                55.34
99992      44.66                45.58
99992      52.06                41.40
99992      36.77                53.86

（所影响的行数为 6 行）
*/

create table tb(CI varchar(10),AssessedValueUP decimal(18,2),AssessedValueDW decimal(18,2))
insert into tb values('99991', 45.973 ,43.262)
insert into tb values('99991', 47.862 ,43.262)
insert into tb values('99991', 62.436 ,74.352)
insert into tb values('99991', 47.098 ,55.342)
insert into tb values('99991', 80.94  ,57.972)
insert into tb values('99992', 53.856 ,62.436)
insert into tb values('99992', 44.663 ,45.576)
insert into tb values('99992', 52.055 ,41.401)
insert into tb values('99992', 229.23 ,36.768)
insert into tb values('99992', 36.768 ,53.856)
go

--sql 2005
select CI ,AssessedValueUP, AssessedValueDW from
(
  select t.* , px=row_number() over(partition by CI order by AssessedValueUP, AssessedValueDW) from tb t
) m
where px <= 3



drop table tb

/*

CI         AssessedValueUP                         AssessedValueDW
---------- --------------------------------------- ---------------------------------------
99991      45.97                                   43.26
99991      47.10                                   55.34
99991      47.86                                   43.26
99992      36.77                                   53.86
99992      44.66                                   45.58
99992      52.06                                   41.40

(6 行受影响)
*/