return distinct products based on product_id

Right now the query below is retrieving more then one product record. How can I limit the results set to only retrieving the one record per product_ID? Multiple records will be returned from different products, but I only need one product line per product_id. This is MS SQL 2005

 SELECT DISTINCT dbo.Products.Product_ID AS Expr1,
                CASE
                                WHEN dbo.Products.thumbnail      IS NULL
                                OR              dbo.Products.thumbnail = ''
                                THEN dbo.Products.Smimage
                                ELSE dbo.Products.thumbnail
                END AS image                                                                                                            ,
                dbo.Products.ProductTitle                                                                                               ,
                '<img WIDTH="62" src="http://media.companyinc.com/companyinc/SKUimages/small/' + dbo.Products.Smimage + '">' AS URLImage,
                dbo.INV_dropshippers_To_ProductOptions.sku                                                                              ,
                dbo.Products.Discontinued                                                                                               ,
         开发者_如何学C       dbo.Products.CloseOut                                                                                                   ,
                dbo.Products.Special                                                                                                    ,
                dbo.Products.Active                                                                                                     ,
                dbo.Products.location_id                                                                                                ,
                dbo.INV_dropshippers_To_ProductOptions.dropshipper_id                                                                   ,
                dbo.Products.season                                                                                                     ,
                dbo.Products.thumbnail                                                                                                  ,
                dbo.Products.Smimage                                                                                                    ,
                dbo.Products.CustomField2                                                                                               ,
                dbo.Products.pt_type                                                                                                    ,
                dbo.Products.PartNumber                                                                                                 ,
                dbo.Products.RetailPrice AS Price                                                                                       ,
                dbo.skupurchasedreport.Product_Id                                                                                       ,
                dbo.skupurchasedreport.totalprice_date1                                                                                 ,
                dbo.skupurchasedreport.totalprice_date2                                                                                 ,
                dbo.skupurchasedreport.qtypurchased_date1                                                                               ,
                dbo.skupurchasedreport.qtypurchased_date2                                                                               ,
                dbo.skupurchasedreport.totalprice_date3                                                                                 ,
                dbo.skupurchasedreport.qtypurchased_date3                                                                               ,
                dbo.INV_dropshippers_To_ProductOptions.quantity AS currentqty
FROM            dbo.Products
                INNER JOIN dbo.skupurchasedreport
                ON              dbo.Products.Product_ID = dbo.skupurchasedreport.Product_Id
                LEFT OUTER JOIN dbo.Options AS Options_2
                RIGHT OUTER JOIN dbo.INV_dropshippers_To_ProductOptions
                ON              Options_2.Opt_ID = dbo.INV_dropshippers_To_ProductOptions.option_id3
                LEFT OUTER JOIN dbo.Options AS Options_1
                ON              dbo.INV_dropshippers_To_ProductOptions.option_id2 = Options_1.Opt_ID
                LEFT OUTER JOIN dbo.Options
                ON              dbo.INV_dropshippers_To_ProductOptions.option_id1 = dbo.Options.Opt_ID
                ON              dbo.Products.Product_ID                           = dbo.INV_dropshippers_To_ProductOptions.product_id
GROUP BY        dbo.skupurchasedreport.Product_Id                    ,
                dbo.INV_dropshippers_To_ProductOptions.product_id    ,
                dbo.Products.Product_ID                              ,
                dbo.Products.ProductTitle                            ,
                dbo.Products.thumbnail                               ,
                dbo.Products.Smimage                                 ,
                dbo.INV_dropshippers_To_ProductOptions.sku           ,
                dbo.Products.Discontinued                            ,
                dbo.Products.CloseOut                                ,
                dbo.Products.Special                                 ,
                dbo.Products.Active                                  ,
                dbo.Products.location_id                             ,
                dbo.INV_dropshippers_To_ProductOptions.dropshipper_id,
                dbo.Products.season                                  ,
                dbo.Products.CustomField2                            ,
                dbo.Products.pt_type                                 ,
                dbo.Products.PartNumber                              ,
                dbo.Products.RetailPrice                             ,
                dbo.skupurchasedreport.Product_Id                    ,
                dbo.skupurchasedreport.totalprice_date1              ,
                dbo.skupurchasedreport.totalprice_date2              ,
                dbo.skupurchasedreport.totalprice_date1              ,
                dbo.skupurchasedreport.totalprice_date3              ,
                dbo.skupurchasedreport.qtypurchased_date1            ,
                dbo.skupurchasedreport.qtypurchased_date2            ,
                dbo.skupurchasedreport.qtypurchased_date3            ,
                dbo.INV_dropshippers_To_ProductOptions.quantity

Wow do you really want to group by that many fields? Generally when I see SQL statements that have some sort of aggregate and group by almost every column in the table it is a sign that it's probably wrong. It may return the right data for today but tomorrow's a different story. Read up on DISTINCT, Group By, and Inner Queries to handle this appropriately. The proper way to post this question was to give us some data from the table and give us what you want as expected output or desired results. My inclination tells me you want some sort of

SELECT .. FROM .. INNER JOIN (SELECT ...)...GROUP BY SomeField

If you want one result per product ID, then you'll have to take a MAX on all the other columns except product ID, and then group only by product ID. That could cause other problems though, so you need to understand the ramifications, and more importantly, the nature of your data.

I'll try to explain the technique, but will leave up to you to fix your query.

Let us start with a table:

DECLARE @tbl TABLE ( Id int, Color varchar(10) )

INSERT INTO  @tbl
        ( Id, Color )
SELECT  1 ,'BLUE' UNION
SELECT  2 ,'BLUE' UNION
SELECT  3 ,'RED' UNION
SELECT  4 ,'GREEN' UNION
SELECT  5 ,'GREEN' UNION
SELECT  6 ,'GREEN' UNION
SELECT  7 ,'YELLOW' UNION
SELECT  8 ,'YELLOW';

And now I want to list only one item of each color (similar to your problem)

WITH  abcd
        AS ( SELECT Id
                   ,Color
                   ,ROW_NUMBER() OVER ( PARTITION BY Color ORDER BY Id ) AS rn
             FROM   @tbl
           )
  SELECT  Id ,Color
  FROM    abcd
  WHERE   rn = 1

Returns

Id          Color
----------- ----------
1           BLUE
4           GREEN
3           RED
7           YELLOW