Applying a database function to a column

You often create row queries because you want to apply a database function, such as Sum or Max aggregates, to the values in a column for selected rows. The following SQL statement is a query that uses a maximum aggregate:

SELECT Country, MAX(CreateTime) 
  FROM pc_Address
  GROUP BY Country;

To create a positional column that you reference by number, starting from 0, you use the path method on QuerySelectColumns. To create a named column, you use the pathWithAlias method. You use methods on the DBFunction class to apply a database aggregate function to a column. For example, the following Gosu expressions both select the maximum value in the CreateTime column from the Address table. The second expression gives the alias NewestAddr to the column.

QuerySelectColumns.dbFunction(DBFunction.Max(Paths.make(Address#CreateTime)))
QuerySelectColumns.dbFunctionWithAlias("NewestAddr", DBFunction.Max(Paths.make(Address#CreateTime)))

The database performs the aggregation as part of executing the query.

The query builder API inserts a GROUP BY clause in the generated SQL if necessary. The arguments to the GROUP BY clause are the non-aggregate columns that you provide to the select method. If you specify no non-aggregate columns, the query builder does not add a GROUP BY clause.

The database aggregate functions on the DBFunction class are Sum, Max, Min, Avg, and Count. The DBFunction class does not provide First or Last functions. The equivalent of the First function is the FirstResult property of the results that the select method returns. You can use the FirstResult property to simulate the Last function by applying the orderByDescending method on the results. Using FirstResult provides better query performance than stopping a query iteration after the first row.