Take a deeper dive into our thoughts about data analytics, technology, strategy, and developments.

DAS42 Image

New Year, New Functions: MIN_BY / MAX_BY

DAS42 Image
Christopher Cannon

As a Snowflake Elite Services Partner, we keep an eye on new releases to ensure we’re providing our clients with quality services and solutions. One of Snowflake’s most recent releases, 7.1, introduced several exciting new capabilities and functions, one of which really caught our attention: MIN_BY / MAX_BY.

What are the new MIN_BY / MAX_BY Functions?

With these new functions, you can quickly return the value(s) in one column conditionally based on another column's maximum or minimum value. Answering this question has always been possible with often complicated subqueries or Common Table Expressions (CTEs). With MIN_BY and MAX_BY, we get a clean function that performs the same task with much less code. We recommend reviewing Snowflake’s documentation for more details, but an overview of the syntax to use in your SELECT statement appears below.

If you’re replicating these queries, note the schema: TPCH_SF100. Learn more about the process here.

Let's assume we want to return the order key from the order with the highest total price. Without the new MIN_BY / MAX_BY functions, we would have to use a subquery or a CTE to return our answer. Before this release, you may have answered the question with a CTE:

The result returned from this query is:

DAS42 Image

Now, let’s return the same answer with the MAX_BY function. Using far fewer lines of code, our query and result are now:

DAS42 Image

One important point to remember is that this function is not deterministic. That is, if your <col_containing_minimum> or <col_containing_maximum> has multiple records with the same minimum or maximum value, the function may return any one of the corresponding values from the <col_to_return>. To overcome this, the function’s optional parameter allows you to return up to a certain number of values. 

A bug with the optional parameter prevents users from invoking the function when the column you seek to return has a NOT NULL constraint applied. We are communicating with Snowflake support on this and will provide an update when it is resolved. Below is a further explanation by way of an example, so that you know exactly when you can and cannot use this function until the bug is fixed.

Create a table:

Using the column (B) with NOT NULL fails:

Using the column (A) without the constraint succeeds:

MIN_BY / MAX_BY - The Bottom Line

Utilizing Snowflake’s new MIN_BY / MAX_BY functions, we produced the same result using half as much code. These improvements towards cleaner, simpler code are far more readable and can save engineers and analysts substantial time. With more updates on the horizon, the opportunities for optimizing your data are near endless. 

Curious to hear more about how DAS42 can help you make the most of your Snowflake instance? Reach out at, and we’d be happy to help!

Ready to talk about your data needs?

Contact us to start building a data culture.