By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,574 Members | 1,968 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,574 IT Pros & Developers. It's quick & easy.

WHERE question

P: 1
Hi,

I'm using SQL Server 2005.

The code below works fine:

select upc, brand, tpen,
(select max(tpen)
from
(
select top 3 tpen
from genitem
where i = 2 and brand = 'cascadian farm'
order by tpen asc
) as A
) as CTPEN
from genitem

Here's my question: I need to change the (brand = 'cascadian farm') to the brand in each row. e.g. brand = current.row.brand.

I could easily use a cursor and loop through the brands, find all of the associated items, then assign them all the calculated CTPEN. I'd like to avoid this by run the sql against all the rows at once.

Thoughts?

Thanks,

misman
Jan 19 '10 #1
Share this Question
Share on Google+
2 Replies


ck9663
Expert 2.5K+
P: 2,878
If you have a dimension table for BRAND, you might be able to accomplish your requirement by just using JOIN.

Good Luck!!!

~~ CK
Jan 20 '10 #2

nbiswas
100+
P: 149
Try with DENSE_RANK() funciton.


SELECT G.upc
, G.brand
, G.tpen
,CTPEN = X.TPEN
FROM (

SELECT TPEN FROM(

SELECT DENSE_RANK() OVER(PARTITION BY BRAND ORDER BY TPEN ASC) X,TPEN FROM genitem WHERE i=2)) WHERE X=1)X

JOIN genitem G

ON G.TPEN = X.TPEN

Hope this helps.
Mar 24 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.