SELECT item.item_id, ANY_VALUE(uses.tag) tag, COUNT(*) number_of_uses FROM item JOIN uses ON item.item_id, uses.item_id GROUP BY item.item_id
shows the rows in a table called
item, the count of related rows, and one of the values in the related table called
You can think of this
ANY_VALUE() function as a strange a kind of aggregate function. Instead of returning a count, sum, or maximum, it instructs the MySQL server to choose, arbitrarily, one value from the group in question. It's a way of working around Error 1055.
Be careful when using
ANY_VALUE() in queries in production applications.
It really should be called
SURPRISE_ME(). It returns the value of some row in the GROUP BY group. Which row it returns is indeterminate. That means it's entirely up to the MySQL server. Formally, it returns an unpredictable value.
The server doesn't choose a random value, it's worse than that. It returns the same value every time you run the query, until it doesn't. It can change, or not, when a table grows or shrinks, or when the server has more or less RAM, or when the server version changes, or when Mars is in retrograde (whatever that means), or for no reason at all.
You have been warned.