ANY VALUE

suggest change
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 uses.

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.

Feedback about page:

Feedback:
Optional: your email if you want me to get back to you:



Table Of Contents