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)
精彩评论