Compute a table-wide maximum
Find the greatest identifier, number, date, or other comparable column value in a table.
Drizzle ORM · Function
max
max is Drizzle ORM's aggregate helper for selecting the greatest value of a column or SQL expression. It is used inside a select projection and corresponds to SQL MAX.
How it works
Use max with a column or SQL expression inside a select projection. Drizzle emits the SQL MAX aggregate and returns the selected maximum under the alias you provide; for custom SQL, map the result with the column's decoder.
Syntax
max(expression)Parameters
| Name | Type | Description |
|---|---|---|
expression | SQL expression | The column or SQL expression whose greatest value should be calculated. |
Returns
A SQL aggregate expression representing the maximum value of the supplied expression.
Key points
- max computes the greatest value produced by its input expression.
- Import the helper from the main drizzle-orm package.
- Use max as a selected field and give the result a projection name such as value.
- The helper corresponds to the SQL MAX aggregate over the chosen column.
- A custom SQL MAX expression can use mapWith to apply the selected column's result mapping.
- Add groupBy when an aggregate query also selects other columns.
Common mistakes
- When selecting an aggregate together with other columns, use groupBy for the non-aggregated columns.
Example: Select a maximum value
import { max } from "drizzle-orm";
import { users } from "./schema";
const result = await db
.select({ value: max(users.id) })
.from(users);Selects the maximum value of the users.id column.
When to use it
Calculate per-group maxima
Select grouped records and calculate the greatest value within each group.
Map a custom aggregate expression
Represent a MAX operation while retaining a column's result decoder.
Interview questions
QWhat does max do in Drizzle ORM?
max is an aggregate helper imported from drizzle-orm. In a select projection, max(users.id) generates SQL equivalent to MAX(id) and returns the maximum value.
Follow-up: How would you preserve Drizzle's result mapping for a custom expression? Use sql with max and mapWith, passing the relevant column or decoder.
QHow do you use max in a query?
The helper accepts an expression, commonly a table column, and is used in a select projection such as db.select({ value: max(users.id) }).from(users). The result is exposed through the projection key.
Follow-up: What SQL does the helper represent? It represents the database MAX aggregate over the supplied expression.
QWhat should you consider when combining max with other selected columns?
If an aggregate is selected alongside ordinary columns, the ordinary columns must be grouped with groupBy, following SQL aggregation rules. The documentation also shows filtering grouped aggregates with having.
Follow-up: Can max be placed in relational-query extras? No; the documentation says aggregations are not currently supported there, so use core queries.
Related
minavgsumcountsumDistinctsqlgroupByhaving