Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Returning the Top X row for each group


Returning the Top X row for each group

Author
Message
Dave Ballantyne
Dave Ballantyne
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2550 Visits: 8370
Comments posted to this topic are about the item Returning the Top X row for each group



Clear Sky SQL
My Blog
kay
kay
SSC Veteran
SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)

Group: General Forum Members
Points: 243 Visits: 90
A nice quest! I like the solution for its clever use of the index.
However, you depend on a table to deliver the lookup-values for the age-column (in this case master..spt_values ) and you need pre-execution knowledge about all the possible ages in your table (in this case 0...100).
Here's a suggestion:

select * from
(select distinct age as AgeGroup from #RunnersBig) S1
cross apply
(select top 2 * from #RunnersBig where age=agegroup order by time)S2

I suspect it is not as fast as your solution, as it scans the index for the "distinct ages". But it would not require any knowledge about the values in the "ages" column.

I wonder if there is a solution having both, a fast execution with index-seek on the one hand, and a general independence of the value distribution in the age-column.

Kay



Madhivanan-208264
Madhivanan-208264
Mr or Mrs. 500
Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)

Group: General Forum Members
Points: 541 Visits: 476
More methods are available here
http://beyondrelational.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx



Madhivanan

Failing to plan is Planning to fail
Dave Ballantyne
Dave Ballantyne
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2550 Visits: 8370
Kay ,

You are absolutely correct , any solution that relies upon a tally table has to have at least the required number of rows in the said tally table.

If you used a tally table to build all the dates for the next 10 years, you need to ensure that you have at least 3655 (ish) rows.

An alternative to your distinct method would be to grab the max(age) , which will involve reading a single row from the index. Something like this...

with cteTally
as
(
select number from master..spt_values where type = 'P' and number >0 and number <=(Select max(age) from #RunnersBig)
)
select *
from cteTally
cross apply
(

select top 2 * from #RunnersBig where age=number order by time
) as Winners





Clear Sky SQL
My Blog
darryl_ba
darryl_ba
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 14
would this exact code work using TOAD? Or will I get errors. Some keywords dont work using TOAD. Also, how would u sum() up the combined selected top row grouping.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: Administrators
Points: 51744 Visits: 19005
Great job and this is a reference I'll keep around.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
RazvanS
RazvanS
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 43
Thank you for the article, very useful.
Is it safe to say that it only applies to SQL Server 2005 & 2008 but not to 2000?
rockvilleaustin
rockvilleaustin
SSC-Enthusiastic
SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)

Group: General Forum Members
Points: 176 Visits: 125
I got this error message:

Msg 4108, Level 15, State 1, Line 3
Windowed functions can only appear in the SELECT or ORDER BY clauses.

when running the following codes:

select * ,row_number() over (partition by Age order by Time ) as RowN
from #Runners
where row_number() over (partition by Age order by Time ) <=2
order by Age,Rown

From what I can understand, row_number() cannot be used in where clause?
Dave Ballantyne
Dave Ballantyne
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2550 Visits: 8370
@Mihai , Yes this is all 2005 (or greater)

@RockvilleAustin

That code was qualified with "In an ideal world we would be able to execute ..... However we cannot, so the currently suggested....."

So the error is expected.



Clear Sky SQL
My Blog
Idea Deadbeat
Idea Deadbeat
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 275
Not directly related to article - but - an alternative to spt_values:

create table #Number (number int);
with N4000 as (select 0 as Number union all select Number+1 from N4000 where Number <4000
)insert into #number select * from N4000 option (MAXRECURSION 4000);
create index ix_N on #Number (Number);


with cteTally
as
(
select Number from #Number where number >0 and number <=(Select max(age) from #RunnersBig)
)
select *
from cteTally
cross apply
(

select top 2 * from #RunnersBig where age=number order by time
) as Winners
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search