Geek Answers Handbook

SQl Request Task -

I have problems in the request:

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 request:

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

This is the 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; at this time I want to get Max Date ie 12-08-2011 .

Thus, the desired result should be as follows:

  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

Please help me ... How to write a request for this situation?

Thank you in advance

+4
source share
2 answers
  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
+3
source

Use this query:

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

More articles:


All Articles