I am trying to retrive records based on the latest time stamp and discard duplicate records. But I am still getting duplicate records back even after using BY HIGHEST. Please, can someone hep out.? Here is my code:

 
TABLE FILE SALES
PRINT
     COMPUTE NS_AMOUNT/D20.2C = ( SALES.SALES_LARGE.S_AMOUNT * SALES.CUR_RATE.RATE ); OVER
     SALES.SALES_LARGE.STOCK_CNT/I6C OVER
     COMPUTE N_OSAMT/D20.2C = ( SALES.SALES_LARGE.OSAMT * SALES.CUR_RATE.RATE ); OVER
     COMPUTE NSTOCK_AMT/D12.2 = ( SALES.SALES_LARGE.STOCK_AMT * SALES.CUR_RATE.RATE ); 

BY  SALES.SALES_LARGE.LOCATION_CD NOPRINT
BY  SALES.SALES_LARGE.STORE_NM
BY  SALES.SALES_LARGE.RTE1_CD
BY  SALES.SALES_LARGE.RTE2_CD
BY  SALES.SALES_LARGE.PLAN
BY  SALES.SALES_LARGE.TYPE_CD
BY  SALES.SALES_LARGE.STORE_NM
BY  SALES.SALES_LARGE.TRANS_DT
BY  HIGHEST 1 SALES.SALES_LARGE.SALES_TIME NOPRINT

WHERE SALES.SALES_LARGE.LOCATION_CD EQ &LOCATION_CD.(OR(FIND SALES.SALES_LARGE.LOCATION_CD IN SALES)).Location:.;
WHERE SALES.SALES_LARGE.STORE_NM EQ &STORE_NM.(OR(FIND SALES.SALES_LARGE.STORE_NM IN SALES)).Store Name:.;
WHERE SALES.SALES_LARGE.PLAN EQ &PLAN.(OR(FIND SALES.SALES_LARGE.PLAN IN SALES)).Plan:.;
WHERE SALES.SALES_LARGE.TYPE_CD EQ &TYPE_CD.(OR(FIND SALES.SALES_LARGE.TYPE_CD IN SALES)).Type:.;
WHERE SALES.SALES_LARGE.CY_CD EQ '&CY_CD.(FIND SALES.SALES_LARGE.CY_CD IN SALES).Cy:.';

This message has been edited. Last edited by: Kerry,

quote:

BY HIGHEST 1 SALES.SALES_LARGE.SALES_TIME NOPRINT

This shold be your first sort

Thanks for the suggestion.

I have tried it but it's still bringing back multiple records with same BY FIELDS values.

umun,
Try using just "BY HIGHEST TimeStamp"
Instead of "BY HIGHEST 1 TimeStamp"

ERINP

quote:

Originally posted by ERINP:
umun,
Try using just "BY HIGHEST TimeStamp"
Instead of "BY HIGHEST 1 TimeStamp"

ERINP

Tried but no success.

umun,
Obviously you have created an example using the sales file...can you post the full code you are trying to use? SO I can see if something else within it may be causing this issue. If the first BY field is BY HIGHEST SALES.SALES_LARGE.SALES_TIME it should return the highest time.

I just noticed that you are using PRINT which would return multiple records that have the same value for SALES.SALES_LARGE.SALES_TIME BUT if you were to use a SUM then it would return only one record for the BY HIGHEST SALES.SALES_LARGE.SALES_TIME.

ERINP

This message has been edited. Last edited by: ERINP,