July 24, 2009 at 6:14 pm
I have a temporary table with :
ProductId,count(p.ProductId) AS cnt, p.Productname,p.productbrand
With possible data like :
1, 9 ,Bath, Brand A
5, 8 ,kitchen, Brand B
7, 8 ,Bedroom Brand A
20,6 ,Charlies Bedroom, Brand C
26,6 ,Kitchen A , Brand C
30,1 ,Gas A, Brand A
31,1 ,Gas B, Brand B
32,1 ,Fire , Brand D
So its essentially ordered by the number of times it was ordered (cnt)
How do I in Transact SQL loop thru this list bearing in mind this is in a temporay table called temp
Now I need to loop thru and select the top X (Constant at the top of the SP in this eg its 6). making sure that for example with quantities 9,8,8,6,6 are in the top 5 but i need to then determine from the rest which one fills the last remaining 1 spot (to make up X items in this eg its 6), using the brand as a filter then i need to use a date in an ordered table (the last time this was ordered ie; max(orderID).
I cant guarantee the data so i need to check to find the top 6 it may be that there is a straight win to the 6 spots. but when there's more than one and we havent reached 6 i need to filter tyhese remaining items to fill the rest to make it up to 6.
Ii hope you understood that ,any quetsions pleasde ask. I need to sort this out for work pretty quickly, my head is on the chopping board.
Darryl
Darryl Wilson
darrylw99@hotmail.com
July 24, 2009 at 6:47 pm
One: You do not need to use a cursor to get data back in a specific order. Here's your data loaded to a temp table and a query to return the top 6 rows in order by the "Cnt" values. It presumes you've already loaded the data from the original database tables including the item count. set @HowMany = 6
--drop table #temp
create table #temp (ProductId int
,cnt int
,Productname char(20)
,productbrand char(20))
insert #temp
select 1, 9 ,'Bath', 'Brand A' union all
select 5, 8 ,'kitchen', 'Brand B' union all
select 7, 8 ,'Bedroom', 'Brand A' union all
select 20,6 ,'Charlies Bedroom', 'Brand C' union all
select 26,6 ,'Kitchen A' , 'Brand C' union all
select 30,1 ,'Gas A', 'Brand A' union all
select 31,1 ,'Gas B', 'Brand B' union all
select 32,1 ,'Fire' , 'Brand D'
select top 6 ProductID
,Cnt
,ProductName
,ProductBrand
from #temp
order by Cnt desc
Two: It may not even be necessary to put the intermediate results in a temp table; the results of whatever query was used to populate that table could be ordered as a final result-set directly. Perhaps you could try doing that and post what you come up with, whether it works as desired or not.
Three: I'm not clear on how you want to resolve ties on the Count field (the code above doesn't specify anything to do that -- the sixth row is indeterminate). You talk about using the brand as "a filter" and then depend on a date field and the max(ID), but I don't understand the relationships or where that data resides. Overall, I'd guess you could join this data to whereever the tie-breaker data is and then simply include those other field (or calculations) in the "order by" clause.
Finally, for future reference, please take a look at this link for better assistance in answering your questions:http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply