SQL Query Help / MAX Function
I'm new to sql. My results have multiple lines because a new line is added on the last day of each month to the PS_DIST_LN table. What I want to do is select the record with the most recent accounting_dt in this table. I thought I could use MAX on the accounting_dt field but keep getting errors. Any help would be greatly appreciated.
Also if there is a better way to write this query, I'd appreciate those comments as well. I'm new to this want am looking to learn the right way to do these things. Thanks again for your help.
from PS_ASSET pa,
where pa.BUSINESS_UNIT = 'A0056'
and pa.ASSET_ID = '000000000003'
and pb.BOOK = 'PERFORM'
and pdr.book = 'PERFORM'
and pdl.book = 'PERFORM'
and pdr.FISCAL_YEAR = '2014'
and pdr.ACCOUNTING_PERIOD = '12'
and pb.ASSET_ID= pa.ASSET_ID and pb.BUSINESS_UNIT = pa.BUSINESS_UNIT
and pdr.ASSET_ID= pa.ASSET_ID and pdr.BUSINESS_UNIT = pa.BUSINESS_UNIT
and pdl.ASSET_ID= pa.ASSET_ID and pdl.BUSINESS_UNIT = pa.BUSINESS_UNIT
and pdl.distribution_type = 'DE'
BU DEPT INSERVDT IDNO DSCR LIFE METH COST DEPREC ACCDP ACCT_DT
A0056 123 01-JAN-14 000000000003 Test 72 SL 500 7.36 659.75 31-JAN-14
A0056 123 01-JAN-14 000000000003 Test 72 SL 500 7.36 659.75 28-FEB-14
A0056 123 01-JAN-14 000000000003 Test 72 SL 500 7.36 659.75 31-MAR-14
A0056 123 01-JAN-14 000000000003 Test 72 SL 500 7.36 659.75 30-APR-14
A0056 123 01-JAN-14 000000000003 Test 72 SL 500 7.36 659.75 31-MAY-14
Try something like this:
FROM ( SELECT Pa.Business_Unit
, ROW_NUMBER ( )
OVER ( PARTITION BY Pa.Business_Unit
ORDER BY Pdl.Accounting_Dt DESC )
FROM Ps_Asset Pa
, Ps_Book Pb
, Ps_Depr_Rpt Pdr
, Ps_Dist_Ln Pdl
WHERE Pa.Business_Unit = 'A0056'
AND Pa.Asset_Id = '000000000003'
AND Pb.Book = 'PERFORM'
AND Pdr.Book = 'PERFORM'
AND Pdl.Book = 'PERFORM'
AND Pdr.Fiscal_Year = '2014'
AND Pdr.Accounting_Period = '12'
AND Pb.Asset_Id = Pa.Asset_Id
AND Pb.Business_Unit = Pa.Business_Unit
AND Pdr.Asset_Id = Pa.Asset_Id
AND Pdr.Business_Unit = Pa.Business_Unit
AND Pdl.Asset_Id = Pa.Asset_Id
AND Pdl.Business_Unit = Pa.Business_Unit
AND Pdl.Distribution_Type = 'DE' )
WHERE Rn = 1;
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
Tags for this Thread
Click Here to Expand Forum to Full Width