Since there is nothing in the official CAP documents about min and max functions, I figured out the following syntax:
const result1 = await cds.run(`SELECT *, MAX(counter) FROM ${myTable} LIMIT 1`) //returns array
const result2 = await SELECT.one.from(myTable, [`MAX(counter)`]).columns('*') //returns object
const result3 = await SELECT.one.from(myTable).columns('MAX(counter)') //returns object containing only the max counter value