Appian version of below SQL query

There are 2 primary options here:

Option 1 (most scalable), create a view in the database which contains the query that matches your main CDT, but points to the view instead.  Then utilize a simple a!queryEntity() over the view CDT to always return the max row.  Best for overhead and scalability.  I also always recommend for views, changing the "SELECT *" into a list of all your columns specifically such as "SELECT id, userName, amount, date.." etc, as changes to the table structure will not automatically be reflected in your view and this can cause confusion for debugging without being able to see which columns are listed (the view would have to be re-published as-is, no changes, utilizing "*" if you change the underlying table structure later).

Option 2 requires querying all values into Appian to utilize the max() function, then a second query to the data source to filter for the max value you are looking for.  If you are concerned with scalability (as we normally are) and your data set growing, this method will limit at 1 MB of data during the initial query, and slow down as it approaches that number.  For cases where, say, users enter 1 to 100 rows per request, and you want to find the max row of a specific request, this should be fine.  Essentially if the submissions are unlimited, you will want to steer to option 1 (I like Option 1 for all of these cases anyway).

Option 2 Sample:

a!localVariables(
  local!yourDataPoint: "id", /* change to your data point */
  local!max: tointeger(
    max(  
      property(
        a!queryEntity(
          entity: cons!COE_DS_SAMPLE, /* change to your data store */
          fetchTotalCount: false,
          query: a!query(
            paginginfo: a!pagingInfo(1,-1),
            selection: a!querySelection(
              columns: {
                a!queryColumn(
                  field: local!yourDataPoint
                )
              }
            )
          )
        ).data,
        local!yourDataPoint,
        0
      )
    )
  ),
  local!maxRow: a!queryEntity(
    entity: cons!COE_DS_SAMPLE, /* change to your data store */
    fetchTotalCount: false,
    query: a!query(
      paginginfo: a!pagingInfo(1,1),
      filter: a!queryFilter(
        field: local!yourDataPoint,
        operator: "=",
        value: local!max
      )
    )
  ).data,
  
  local!maxRow
)

Thanks Chris for the elaborate response. For now we had to go with second approach. In fact, first approach is reasonable, I have recommended and asked them for that equivalent view to SQL query.

But, max() doesn't work with dates. Any direct function to return max date, rather than sorting in descending order and then picking first item which I am doing right now?

Thanks Chris for the elaborate response. For now we had to go with second approach. In fact, first approach is reasonable, I have recommended and asked them for that equivalent view to SQL query.

But, max() doesn't work with dates. Any direct function to return max date, rather than sorting in descending order and then picking first item which I am doing right now?