WHERE question

[misman]

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.bra nd.

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

[ck9663]

If you have a dimension table for BRAND, you might be able to accomplish your requirement by just using JOIN.

Good Luck!!!

~~ CK

[nbiswas]

Solution to where question

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.