Saturday, December 11, 2010

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









No comments:

Post a Comment