数据按条件分组后最前N条

最近遇到一个数据库方面的问题,按要求分组后取前N条记录,如果没有N条则将现有的记录全部取出来,

Create table TopGroup

(

    ID int not null identity(1,1),

    [Type] varchar(128) not null,

    [Value] Numeric(10,2)

)

GO

 

Insert into TopGroup(Type,Value)

Select 'C',34 Union

select 'C',45 Union

select 'C',88 Union

select 'C++',75 Union

select 'C++',85 Union

select 'C++',95 Union

select 'Java',87 Union

select 'Java',97 Union

select 'Java',63 Union

select 'Java',60 Union

select 'Java',82

 

 

Select * from TopGroup

 

--分组后取Value值较大的前两条记录

Select A.ID,A.Type,A.Value

From TopGroup A

Where (

       Select Count(*)

       From TopGroup B

       Where A.Type=B.Type And A.Value<=B.Value

       )<=2

 

GO

 

Drop Table TopGroup