SQL queries the first few/specified number of groups

Note: Under sqlserver

Create Table Test

(

Areaid int,

Score int

)

Insert into test select 0, 10

Union all select 0, 20

Union all select 0, 30

Union all select 0, 40

Union all select 0, 50

Union all select 1, 10

Union all select 1, 20

Union all select 1, 30

Union all select 1, 40

Union all select 1, 50

Union all select 2, 10

Union all select 2, 20

Union all select 2,30

Union all select 2,40

Union all select 2,50

Go

Select * from test

-- The first method is applicable to SQL2000 AND ITSCodeAs follows:

Select * from test

Where checksum (*) in (select top 3 checksum (*) from Test B where a. areaid = B. areaid order by score DESC)

-- The second method is to use the sql2005 function row_number. The Code is as follows:

(1)

With test1

(

Select *,

Row_number () over (partition by areaid order by score DESC) as 'rownumber'

From Test

)

Select *

From test1

Where rownumber between 1 and 3;

(2)

Select areaid, score from (

Select *, row_number () over (partition by areaid order by areaid DESC) row_number from test) A where row_number <6 and row_number> 2

-- The third method is to use sql2005's cross apply. The Code is as follows:

Select distinct T. * from test

Cross apply

(Select top 3 areaid, score from test

Where a. areaid = areaid order by score DESC) as t

 

 

SQL retrieves the first row of data for each group

Select a. * From Table1 a inner join (
Select max (a + B) Time from Table1
Group by c) B
On a. A + A. B = B. Time

 

Of course, the row_number in sql2005 provides a variety of solutions to many problems:

Select * from (select T. *, rank ()
Over (partition by T. A order by T. B DESC) as drank
From Table1 t) A where drank = 1