This article will show you, how to get some value based on the maximum metric from a set of values.
FOR_MAX() function takes two arguments: metric and value. Returns a value corresponding to the maximum metric in the same row from a set of values.
The value argument can be one of the following types:
The value argument can be of any type except STRING.
- Returns NULL if:
- there are no input rows
- the metric column has only NULL values
- the value corresponding to the maximum metric is NULL.
- Returns NaN if the input contains a NaN.
We have a payments table that stores the records of payments by customers, along with discounts applied during the payment:
It will create a table as shown below:
#Case 1: FOR_MAX() on the whole table
For example, let's check the price for the highest discount applied to it:
It will return the following output (NULL in this case):
#Case 2: FOR_MAX() with GROUP BY clause
For this example, we use a GROUP BY clause to group the customers, then use FOR_MAX() to get a discount for the highest price paid by each customer.
Which will give the following result: