Generating the top X rows from each group.

FYI: CROSS Apply works better than Filter in terms of performance

/* To display the records using Filter */
/* Create a temporary table */

CREATE TABLE #Runners
(
Runner integer NOT NULL,
Time integer NOT NULL,
Age integer NOT NULL
)


/* Insert sample records into the table*/

INSERT INTO #Runners

SELECT 1 , 10 , 20

UNION ALL

SELECT 2 , 15 , 20

UNION ALL

SELECT 3 , 11 , 20

UNION ALL

SELECT 4 , 12 , 30

UNION ALL

SELECT 5 , 18 , 30

UNION ALL

SELECT 6 , 9 , 40

UNION ALL

SELECT 7 , 16 , 40

UNION ALL

SELECT 8 , 13 , 30

UNION ALL

SELECT 9 , 13 , 30;

/* To retrieve the top X from each group using filter */

/* If both the time has the same time then the row number will be different for each same row */

with cteRunners
as
(
select * ,row_number() over (partition by Age order by Time ) as RowN
from #Runners
)

Select * from cteRunners
where RowN <=2
order by Age,Rown;


/* To give same row number for the same time present use DENSE_RANK */

with cteRunners 
as
( 
select *,DENSE_RANK() over (partition by Age order by Time ) as RowN 
from
#Runners
) 

Select  * from cteRunners 
where  RowN <=2 
order by Age,Rown 


/* To drop the temporary table */
DROP  TABLE #Runners/* Using CROSS APPLY */

IF OBJECT_ID('tempdb..#RunnersBig') IS NOT NULL drop table #RunnersBig

/* Create a table and enter the millions of sample data for checking the performance */
Create Table #RunnersBig
(
RunnerId integer identity ,
Time integer not null,
Age integer not null
)
go

insert into #runnersbig ( Time , Age )
select top 1000000 ABS ( checksum ( newid ()))% 1000 ,
ABS ( checksum ( newid ()))% 99
from sys . columns a cross join sys . columns b cross join sys . columns c
go
/* Create a Index */
create index idxrunnersbig on #runnersbig ( age , time ) include ( runnerid );

with cteN
as
(
select distinct Age number from #RunnersBig
)
Select *
from cteN cross apply ( Select top ( 2 ) * from #RunnersBig where #RunnersBig . Age = cteN . number order by Time ) as runners
order by cteN . number , runners . Time