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

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

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

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

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.

You can use below query to find your desired result.

SELECT  ProductPriceId, ProductId, CurrencyId, CustomerGroupId, PriceTierId, List, Selling, Bulk,

        RANK() OVER (PARTITION BY ProductId ORDER BY Selling DESC) AS Rnk

FROM    ProductPrice

WHERE  Rnk=1