how to find min selling price record in sql server 2008?

Question

ProductPrice table:
ProductPriceId,ProductId,CurrencyId,CustomerGroupId,PriceTierId,List,Selling,Bulk   



868      1      1      NULL   NULL      45.00      42.00      42.00      
869      1      1      2      NULL      39.00      36.00      33.00      
870      1      3      NULL   NULL      48.00      45.00      45.00      
871      1      1      5      NULL      40.00      40.00      40.00      
872      2      1      NULL   NULL      50.00      48.00      48.00      
873      2      3      NULL   NULL      50.00      50.00      50.00      
874      2      1      2      NULL      45.00      45.00      45.00      
875      2      1      5      NULL      56.00      56.00      56.00      

product id one have 4 record i want to find min selling price record in the table . Eg
product id =1 result is :

869 1   1   2   NULL    39.00   36.00   33.00   

plz help me

Answer 1

The below will return multiple rows in the event of a tie. If you don't want that use Row_number() in place of Rank()

;WITH cte AS
     ( SELECT  ProductPriceId,
              ProductId      ,
              CurrencyId     ,
              CustomerGroupId,
              PriceTierId    ,
              List           ,
              Selling        ,
              Bulk           ,
              RANK() OVER (PARTITION BY ProductId ORDER BY Selling) AS Rnk
     FROM     ProductPrice
     )
SELECT ProductPriceId ,
       ProductId      ,
       CurrencyId     ,
       CustomerGroupId,
       PriceTierId    ,
       List           ,
       Selling        ,
       Bulk
FROM   cte
WHERE  Rnk=1

Answer 2

SELECT TOP 1 
    * 
FROM 
    ProductPrice 
WHERE 
    ProductId = 1 
ORDER BY 
    Selling ASC

Answer 3

select t1.*
from ProductPrice t1
where not exists(select *
                 from ProductPrice t2
                 where t2.Selling<t1.Selling and t2.productid=t1.priductid)

This will return all minimum selling price records for each product id. If there are multiple such rows then it will return multiple.