LookUpRows without WHERE clause

Question

I want to return some rows from a data extension (the rows contain titles and links for news items to populate a newsletter)

I was going to use LookupOrderedRows returning all rows from this data extension sorted by date descending. However I do not wish to use a WHERE clause - I want all of them. Should I be using a different Ampscript function or is there a way to not use a WHERE clause in LookupOrdereRows, such as this:

set @rows = LookupOrderedRows("UCAS_Media_News_Items_DE",@num,"[date] desc")

Answer 1

Unfortunately there is no way to run a LookupOrderedRows without a where statement.

The easiest way to accomplish what you want is by adding in a column in your DE that is something like 'Secret' and make the value '1' for ALL records.

You can then do a LookupOrderedRows using that column as the where statement.

For example:

set @rows = LookupOrderedRows("UCAS_Media_News_Items_DE", @num, "[date] desc", "Secret", 1)

This will return all the rows as all the rows will contain a value of 1 in the 'Secret' column.

---

Another option is to instead use the SSJS Rows.Retrieve() method.

Example:

var yourDE = DataExtension.Init("UCAS_Media_News_Tems_DE");
var data = yourDE.Rows.Retrieve();

This function returns information in an array of objects in JSON format:

[
    {
        "yourColumn1":"yourValue1",
        "yourColumn2":"yourValue2",
        "yourColumn3":"yourValue3"
    }
]

Depending on your use case and familiarity with SSJS, it may be better to stick with the Ampscript. I personally would stick to the AMPScript solution as it is quicker and more efficient - especially if it is on an email as SSJS runs very slow on emails at send time. This can greatly delay your emails at send time, or potentially cause them to time out and error.