A query using MAX(activity_date) grouped by customer and state still returns multiple distinct 'latest' rows for the same customer/state combination, instead of collapsing to the single genuinely most-recent date — even the same date appears to be picked incorrectly, and adding DISTINCT doesn't fix it either. What's actually wrong?

All FAQ questions / Reporting and analytics

Answer

This strongly suggests the date column is actually stored as a text/VARCHAR2 string rather than a genuine DATE type, meaning MAX() (and any comparison/sorting) is being evaluated as a string comparison rather than a true chronological comparison — which can produce results that look wrong for dates written in inconsistent or non-lexicographically-sortable text formats. Convert the column explicitly with TO_DATE(activity_date, '<matching format mask>') inside the MAX() aggregation, and ideally have the underlying column genuinely typed as DATE rather than text going forward, to get correct chronological aggregation instead of a misleading string-based one.

Documentation-based answer. Confirm the product and release before applying it. This answer has not been verified in an IFS environment.

← Browse Reporting and analytics questions