Top 5 From Per group in one Select?

Question

Post reply

Top 5 From Per group in one Select?

gex

SSC Veteran

Points: 238
More actions
September 20, 2005 at 12:20 am

#91306

all, (or anyone for that matter...)
I am a little stumped when it comes to attempting the following:
What I need is one select statment that selects the top 5 records for each group.
i.e. I would like the top 5 stores per region for the following query:
select Store_id, Store, Region, [% Chg Cap],[% Chg Rad]
from Performance_Variance
where [% chg RAD]< 0
and Month_end = '2005-09-14 23:59:59'
and Level = 'store'
Order by Region, Store_id
Any help on this is greatly appreciated!
tx
gex
gex

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Simon-220850 SSCrazy Points: 2789 More actions · Answer 1

Hi Luke,

How about

SELECT A.Store_id, A.Store, A.Region, A.[% Chg Cap],A.[% Chg Rad]

FROM Performance_Variance A

WHERE A.[% chg RAD]< 0

AND A.Month_end = '2005-09-14 23:59:59'

AND A.Level = 'store'

AND A.store_id IN

(SELECT TOP 5 B.store_id

FROM Performance_Variance B

WHERE B.region = A.region

ORDER BY B.[% Chg Cap] (OR whatever other criteria you want to use to determine the top))

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 2

what about using a cursor

create table temp#

(OrderID int,CustomerId varchar(10))

declare @CustomerId varchar(10)

DECLARE cur_CustomerList CURSOR FOR

SELECT

distinct CustomerId from orders

OPEN cur_CustomerList

FETCH NEXT FROM cur_CustomerList into @CustomerId

WHILE @@FETCH_STATUS = 0

BEGIN

insert into temp# select top 5 orderid ,CustomerId from orders where CustomerId = @CustomerId

FETCH NEXT FROM cur_CustomerList into @CustomerId

END

CLOSE cur_CustomerList

DEALLOCATE cur_CustomerList

select * from temp#

drop table temp#

My Blog: http://dineshasanka.spaces.live.com/

gex SSC Veteran Points: 238 More actions · Answer 3

Addict,

Your cursor worked like a charm!

Ta for the example.

gex

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 4

my name is nto addict, it is Dinesh Asanka

addict is label given by the site owners

My Blog: http://dineshasanka.spaces.live.com/

gex SSC Veteran Points: 238 More actions · Answer 5

I suppose "newbie' says it all for my label.....

tx for the help...

gex

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Please learn to use a set based approach. There's just no need for a cursor in that select.

gex SSC Veteran Points: 238 More actions · Answer 7

Please EXPLAIN how to use a set based approach, if There's just no need for a cursor in that select...

gex

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Here's a working exemple :

SELECT O.XType

, O.name

FROM dbo.SysObjects O

WHERE ID IN (SELECT TOP 5 ID FROM dbo.SysObjects O2 WHERE O2.XType = O.XType order by O2.Name)

ORDER BY O.XType, O.Name

The cursor method is slower because you have to run one statement per line, insert into a temp table, then select from the temp table instead of simply selecting.

gex SSC Veteran Points: 238 More actions · Answer 9

RGR'us,

Thanks for the example. It really is a lot simpler than having to go through all the coding for a cursor (Although the Cursor did work too).

Much appreciated.

gex

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

Now that I think of it, I could probably teach my dog to read and fetch that info from me...

Guess which one is faster between the 2?

The dog, by a nose over the cursor .

Joking aside, you should really do all you can to not use a cursor. They are really not the most optimal tool to use on the server.

gex SSC Veteran Points: 238 More actions · Answer 11

While your knowledge on the subject is undoubtedly at a high level, your ability to convey this in a condescending manner is far higher..

If you want to share your thoughts, do so in a tone that will encourage participation, not in one that will make the enquirer feel belittled/ridiculed and therefore think twice about posting again for fear of the same treatment.

Something to think about.

gex

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

Sorry, didn't meant it to come out that way.

Resume -

Avoid cursors like the pest on sql server. SS is built to work with sets and is optimized for that purpuse only. Cursor add a lot of overhead to work with and can cause serious problems if they take too much ram (The cursor is basically a select into temp table, the server has to keep that table in sync with the base tables when you update/delete/insert data from the cursor, hence the huge overhead).

sushila SSC-Dedicated Points: 35293 More actions · Answer 13

Luke - in remi's defense I should say that he's very seldom condescending - the only time I've seen him really lose his poise is when someone doesn't get "it" after the 100th post...I think you read far too much in his response - it was just a lighthearted approach in getting across the "CursorsAreEvil" point...

If you want to know the true definition of "condescending" combined with withering sarcasm that's designed to make a person feel like he/she should never have been born in the first place take a dekko at some of these links that I'm posting for your edification..

better way to write sql

function to return

failed between operator

id column

trigger vs. referential integrity

**ASCII stupid question, get a stupid ANSI !!!**

gex SSC Veteran Points: 238 More actions · Answer 14

RGR'us & sushila,

Understood that there was no harm intended.

None done.

Remi, I just wanted to set a "boundary" before things "may" have gotten out of hand (although I am sure they wouldn't have).

Now that we are better aquainted, please don't hesitate to provide valuable content to the "not-so-well-informed" (that would be me....) - oh, by the way. Self-ridicule is quite fine...

Thanks again for your contributions, they have certainly made my work a little easier.

gex