如何在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  

产品id一个有4条记录我想在表格中找到最低销售价格记录。 如
产品ID = 1个 结果是：

869 1 1 2 NULL 39.00 36.00 33.00 

plz帮助我

Answer 1

下面将在平局的情况下返回多行。如果你不想代替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) 

在使用Row_number()这将返回所有的最低售价记录，为每个产品ID。如果有多个这样的行，那么它将返回多个。

Answer 4

您可以使用下面的查询来找到您想要的结果。

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