I am creating a view of an existing table to enable a crystal report to run faster.
I have written the SQL
SELECT DISTINCT TOP (100) PERCENT FEE_CODE, VALID_FROM, VARIANT_CONDITION, CURRENCY, PRICE, VALID_TO, DESCRIPTION
GROUP BY CURRENCY, PRICE, APPLICATION, CONDITION_TYPE, SALES_ORG, FEE_CODE, MATERIAL_PRICING_GRP, VARIANT_CONDITION, VALID_TO, VALID_FROM, DESCRIPTION, DELETION_IND
HAVING (MATERIAL_PRICING_GRP = '01') AND (DELETION_IND <> 'X') AND (SALES_ORG = '1000')
ORDER BY FEE_CODE, VALID_FROM DESC, VARIANT_CONDITION, CURRENCY, PRICE, VALID_TO, DESCRIPTION
However this returns multiple lines for each Fee_Code with Variant_Condition, I only want to extract the latest record for the combination which is determined by the Valid_From date, which is not a fixed date. Does anyone know how I need to change the script. thank you in advance for any help, I am sure its obvious when you know how