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
)

TBH, this seems like a lot of running around / bending-over-backwards to do essentially this:

a!localVariables(
  local!maxRow: a!queryEntity(
    entity: cons!COE_DS_SAMPLE,
    query: a!query(
      paginginfo: a!pagingInfo(
        startIndex: 1,
        batchSize: 1,
        sort: a!sortInfo(   /* this should get the max row by "colmn1" in almost all cases */
          field: "colmn1",
          ascending: false()
        )
      )
    )
  ).data,

  local!maxRow
)

Sorting is a great option (including for dates).  I may have been rusty from the long weekend

Sorting is a great option (including for dates).  I may have been rusty from the long weekend