Use ANY_VALUE() for simpler grouping of results in Fabric Data Warehouse (Generally Available)
Fabric Data Warehouse now supports the ANY_VALUE() aggregate, making it easier to write readable, efficient T-SQL when you want to group by a key but still return descriptive columns that are functionally the same for every row in the group.
What is ANY_VALUE()?
ANY_VALUE() is an aggregate or analytic function that returns an arbitrary value from each group or partition. It’s designed for scenarios where you’re aggregating values by a key (for example, GeographyID) and you want to carry through descriptive attributes (for example, city, state, country) that don’t change within the groups defined by that key. If the value is truly constant within the group (functionally dependent on the grouping key), ANY_VALUE() makes your intent explicit: “group by the key and just bring along the descriptive columns.”
The following is a practical example to see how ANY_VALUE() simplifies common aggregation scenarios. Suppose you want to calculate the total revenue from taxi trips for each geographic area (specifically grouping by GeographyID). At the same time, you’d like to include descriptive details such as the associated city, state, and country for each group. Since these descriptive columns are functionally dependent on GeographyID and don’t vary within each group, ANY_VALUE() allows you to easily project them in your results without cluttering your GROUP BY clause or resorting to less intuitive aggregates.
Figure: Using ANY_VALUE() to project descriptive columns while aggregating trips by GeographyID.
Use ANY_VALUE() when the chosen column is functionally dependent on the grouping key (for example, City is constant for a given GeographyID). If the values can legitimately vary within the group, pick an aggregate that matches the business rule (for example, MIN(), MAX(), or a windowing approach).
Adopting this pattern offers several important advantages when writing aggregation queries. Consider the following key benefits:
- Cleaner GROUP BY: You only group by what defines the aggregation (GeographyID), instead of repeating every descriptive column. Many unnecessary columns in GROUP BY might cause performance issues.
- Clearer intent: You’re saying the descriptive columns are “along for the ride,” not part of the grouping logic.
- Avoid unnecessary workarounds: You don’t need to use MIN()/MAX() just to select a representative value when the value is already constant within the group. Also, the developers who read this query would not need to think about why someone would need a maximal value of city and accidentally change this if they don’t understand the true intention of code.
Conclusion
ANY_VALUE() helps keep aggregation queries focused on what defines the group, while still letting you return the descriptive fields your report or dashboard needs. Try it in your Fabric Data Warehouse workloads to simplify query text, reduce GROUP BY noise, and make maintenance easier.
For syntax, examples, and additional details, refer to the ANY_VALUE (Transact-SQL).
