ampscript get max value of column in dataextension

Question

I'm trying to get max date value of a dataextension by LookupOrderedRows getting first row of that rowset.

How can I get max/min date value of a dataextension with ampscript?

Thank you!

Answer 1

For an AMPScript only solution, you will need to put in a field into your DE that ALL rows contain. E.g. I usually include a column labeled 'Secret' that has a value of 1 for all of my rows.

From there you will be able to do your lookuporderedrows() function. As @KennethWagner has stated, there is a default limitation of around 2K records on the lookups, but if you use DataExtensionRowCount() on the DE you are looking up on, you can get around this limitation. Keep in mind, this can have drastic effects if you are looking up against a significantly sized DE.

So basically, you would use the following:

LookupOrderedRows(@YourDE, DataExtensionRowCount(@YourDE), 'Date desc', 'Secret', 1)

Ref: Sprignature moves from the Amazing @AdamSpriggs is a great resource for Reference on everything SFMC and for best practices.

You can also utilize SQL Queries (via Automation Studio or Interactions in Email Studio) to pull this value, using something like:

SELECT TOP 1 Date
FROM YOUR_DE

or you can utilize SSJS Data Extension Retrieve option (Note: Not for use inside of an email, only in Landing pages/Microsites/Script interaction)

var YourDE = DataExtension.Init('YourDE');
var results = YourDE.Retrieve({Property:"CustomerKey",SimpleOperator:"notEqual",Value:"NotValue"});

SSJS solution is a bit clunky and can wind up slowing things down, so unless you absolutely need it or are more comfortable in SSJS I would highly recommend SQL or the AMPScript above.