January 13, 2012 at 4:41 pm
I have a select statement that JOINs two tables, one for Product and one for the Product Colors.
I need to create a select statement that outputs a single row per product with the first color, alphabetically, and that color's corresponding Image.
Without factoring the Image, I know I can do a MIN(ColorName). But MIN(ColorImage) may give me an image for a different color.
I need to find an efficient way to acquire the ColorName and ColorImage from the first color alphabetically.
Any ideas or options would be appreciated. This is for a transaction system with high volume so it needs to perform as efficiently as possible. Thanks!
samples:
(MSSQL 2008)
create table tblTableA (ProductId int)
insert into tblTableA
select 1
union select 2
union select 3
create table tblTableB (ProductColorId int, ProductId int, ColorName varchar(20), ColorImage varchar(50))
insert into tblTableB
select 1,1, 'Red', '1Red.jpg'
union select 2,1, 'Red', '1Black.jpg'
union select 3,1, 'Red', '1Blue.jpg'
union select 4,2, 'Red', '2Red.jpg'
union select 5,2, 'Red', '2Black.jpg'
union select 6,2, 'Red', '2Blue.jpg'
union select 7,3, 'Red', '3Red.jpg'
union select 8,3, 'Red', '3Black.jpg'
union select 9,3, 'Red', '3Blue.jpg'
select a.productid, min(colorname)--, b.colorimage
from tblTableA a
join tblTableB b
on a.productid = b.productid
group by a.productid
January 13, 2012 at 4:55 pm
; WITH CTE AS
(
SELECT A.ProductId , B.ProductColorId , B.ColorImage , B.ColorName
,RN = ROW_NUMBER() OVER(PARTITION BY B.ProductId ORDER BY B.ColorName )
FROM tblTableA A
JOIN tblTableB B
ON A.ProductId = B.ProductId
)
SELECT *
FROM CTE
WHERE RN = 1
This???
January 14, 2012 at 2:23 am
One alternative
Select ProductId , ProductColorId ,ColorImage , ColorName
from tblTableA
Cross apply (Select top(1) ProductColorId , ColorImage , ColorName
from tableB
where tableA.ProductId = tableB.ProductId
order by ColorName) as tabB
Which *could* be more performant if you have an index on tableb(ProductId,ColorName) possibly including ProductColorId and ColorImage. But will also depend on the data-distribution too.
January 17, 2012 at 4:34 pm
Nice, I'll see if I can translate that to my, much more complex, query. Thanks for taking the time to help me!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply