0

Possible Duplicate:
SQL Server: Get Top 1 of Each Group

I have 2 tables in MS SQL. One of tblProducts and tblProductsImage.

And all products have min. 2 pictures. But I want to show only first image, every product.

What can I do this INNER JOIN and SELECT command.

Community
  • 1
  • 1
Güray Yarar
  • 143
  • 2
  • 13
  • 5
    How should the image be selected? Randomly? Most Recent? Best Looking? Also, please include the data available to make that decision. *[And what version of SQL Server?]* – MatBailie Oct 16 '12 at 12:47

2 Answers2

1

If you can use apply you can use query like this, but it's ordered by id

select *
from tblProducts as P
    outer apply
    (
        select top 1 T.Image from tblProductsImage as T
        where T.ProductId = P.Id
        order by T.Id
    ) as PI
Roman Pekar
  • 107,110
  • 28
  • 195
  • 197
1

Try something like this: 

 SELECT * 
FROM   TBLPRODUCTS T1 
       INNER JOIN (SELECT *, 
                          Row_number() 
                            OVER ( 
                              PARTITION BY PRODUCT_ID 
                              ORDER BY X) RN 
                   FROM   TBLPRODUCTSIMAGE)T2 
               ON T1.PRODUCT_ID = T2.PRODUCT_ID 
WHERE  T2.RN = 1
Gidil
  • 4,137
  • 2
  • 34
  • 50
  • 1
    +1 : But just to note... To simplify this, remove one level of sub-query by moving the `T.RN = 1` to the outermost query *(in either the WHERE clause or the JOIN)*, and/or use a Common Table Expression instead of a sub-query. – MatBailie Oct 16 '12 at 13:14