开发者

How to write query for this situation to get the max date?

I have issues in query:

 priceDeatil 
 ProductCode  BusinessUnit  price   DateFrom     DateTo
  10001         ORB          12.00   12-08-2011   31-09-2015
  10001         ORB          21.00   01.08-2011   15-11-2011
  10002         ORB          31.00   01.04-2011   15-08-2012开发者_如何转开发
  10003         ORB          42.00   01.05-2011   15-08-2012

My query is:

 SELECT     BusinessUnit, ProductCode,  DateFrom, DateTo, Price
 FROM         WMPriceDetail
 WHERE     (DateFrom < 'Sep 01 2011') AND (DateTo > 'Sep 01 2011' OR DateTo = '')
 ORDER BY ProductCode

This return

   priceDeatil 
 ProductCode  BusinessUnit  price   DateFrom     DateTo
  10001         ORB          12.00   12-08-2011   31-09-2015
  10001         ORB          21.00   01.08-2011   15-11-2011
  10002         ORB          31.00   01.04-2011   15-08-2012
  10003         ORB          42.00   01.05-2011   15-08-2012

But Here productCode 10001 return two records; That time I want to get the Max Date i.e 12-08-2011 .

So desired result should be like this:

   priceDeatil 
 ProductCode  BusinessUnit  price   DateFrom     DateTo
  10001         ORB          12.00   12-08-2011   31-09-2015
  10002         ORB          31.00   01.04-2011   15-08-2012
  10003         ORB          42.00   01.05-2011   15-08-2012

How to write query for this situation?


 SELECT     BusinessUnit, ProductCode,  DateFrom, DateTo, Price FROM
 (SELECT    BusinessUnit, ProductCode,  DateFrom, DateTo, Price, 
 rank() over (PARTITION BY ProductCode ORDER BY DateFrom DESC) rank_num
 FROM  WMPriceDetail
 WHERE (DateFrom < 'Sep 01 2011') AND (DateTo > 'Sep 01 2011' OR DateTo = '') t 
 WHERE rank_num=1
 ORDER BY ProductCode


Use this query:

select BusinessUnit, ProductCode,  DateFrom, DateTo, Price from WMPriceDetail 
where DateFrom  in (select MAX(datefrom) from WMPriceDetail group by ProductCode)
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜