BigQuery ANY_VALUE with HAVING to Filter Single-Item Orders

Here's another rather rare instance where I've used ANY_VALUE in
BigQuery. It's basically an aggregation function like SUM or COUNT except it retrieves a arbitrary value from the grouping.

I've posted about ANY_VALUE before, but today's query was a bit different.

In the example below, my goal is to find orders that contain a single value, belonging to a particular list of values.

In other words, which order consisted of exactly one item, with that being grapes or oranges?

We aggregate using COUNT to count the number of order lines in an order and ANY_VALUE to pick a random value from the list of order lines.

We then filter the aggregate results using HAVING, keeping only order that have exactly one order line and that order line being a grape or orange.

With ANY_VALUE of a single value being always that value, we can filter the result sets to what we need. It's entirely true that the same can be said of MIN or MAX for instance, but I think it would have been a little less obvious of why it was chosen like that.

As almost always with SQL, there are of course plenty of other ways to achieve the same result.

Found it useful? Subscribe to my Analytics newsletter at notjustsql.com.

Combining ANY_VALUE with HAVING in BigQuery

Comments

Practical SQL

What are GCP Cloud Workflows and how can they help you as a Data Engineer

More from this blog

pre_ and post_operations in Dataform

BigQuery Object Tables: A Practical Introduction

BigQuery BigLake Tables Explained: What They Are and When to Use Them

BigQuery Saves Your Query Results — Here's How to Find Them

Parameters in BigQuery

Command Palette

Comments

Practical SQL

What are GCP Cloud Workflows and how can they help you as a Data Engineer

More from this blog