ANY_VALUE
Returns the value of the expression from some row of the group.
MySQL 8.1
ANY_VALUE(expr)Parameters
- expr
- Expression whose value is taken from any row of the group
Return value
A value of the same type as expr from an arbitrary row of the group
Examples
Any name for each status
MySQL 8.1
SELECT status,
ANY_VALUE(member_name) AS any_member,
COUNT(*) AS members
FROM FamilyMembers
GROUP BY status;Company name by its id
MySQL 8.1
SELECT company,
ANY_VALUE(Company.name) AS company_name,
COUNT(*) AS trips
FROM Trip
JOIN Company ON Company.id = Trip.company
GROUP BY company;Details
The training databases use the ONLY_FULL_GROUP_BY mode: a column that is not in GROUP BY and not wrapped in an aggregate function causes an error. ANY_VALUE removes this error when the value is the same within the group (for example, a company name by its id) or when any value will do. Which row ends up in the result is not defined.