Returning the Top X row for each group

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Dave Ballantyne » Returning the Top X row for each group

34 posts, Page 1 of 4

1 2 3 4 »»»

Returning the Top X row for each group

Rate Topic

Display Mode

Topic Options

Author

Message

Dave Ballantyne

Posted Saturday, December 04, 2010 1:06 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 28, 2013 3:05 AM
Points: 1,943, Visits: 8,229

Comments posted to this topic are about the item Returning the Top X row for each group

Clear Sky SQL
My Blog
Kent user group

Post #1030309

kay

Posted Monday, December 06, 2010 1:22 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 30, 2011 12:19 AM
Points: 212, Visits: 86

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

Post #1030480

Madhivanan-208264

Posted Monday, December 06, 2010 2:29 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, September 12, 2012 5:17 AM
Points: 329, Visits: 461

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

Post #1030495

Dave Ballantyne

Posted Monday, December 06, 2010 3:29 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 28, 2013 3:05 AM
Points: 1,943, Visits: 8,229

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
Kent user group

Post #1030513

darryl_ba

Posted Monday, December 06, 2010 4:59 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, December 09, 2010 5:15 AM
Points: 1, Visits: 11

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.

Post #1030528

Steve Jones - SSC Editor

Posted Monday, December 06, 2010 6:53 AM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 3:19 PM
Points: 31,526, Visits: 13,863

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

Post #1030578

mihai_stoica

Posted Monday, December 06, 2010 7:49 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 31, 2013 11:59 AM
Points: 18, Visits: 20

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?

Post #1030623

rockvilleaustin

Posted Monday, December 06, 2010 7:56 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 04, 2013 8:09 AM
Points: 168, Visits: 109

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?

Post #1030630

Dave Ballantyne

Posted Monday, December 06, 2010 8:02 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 28, 2013 3:05 AM
Points: 1,943, Visits: 8,229

@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
Kent user group

Post #1030635

Idea Deadbeat

Posted Monday, December 06, 2010 8:06 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 17, 2013 9:53 AM
Points: 14, Visits: 247

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

Post #1030641

« Prev Topic | Next Topic »

34 posts, Page 1 of 4

1 2 3 4 »»»

Permissions