FOR_MAX

9min
This article will show you, how to get some value based on the maximum metric from a set of values.
OverviewFOR_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:
INT
LONG
FLOAT
DOUBLE
DATE 
TIMESTAMP 
The value argument can be of any type except STRING.
💡Special cases:
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.
ExamplesWe have a payments table that stores the records of payments by customers, along with discounts applied during the payment:
Create a Table
|
CREATE TABLE payments (
    paymentid int,
    customer_name string,
    price real,
    discount real
);
INSERT INTO payments (paymentid, customer_name, price, discount)
VALUES 
(1, 'Alex', 280.12, 0.1),
(2, NULL, 35.75, NULL),
(3, 'Alex', 45.1, 0.05),
(4, 'Alex', NULL, 0.4),
(5, 'John', NULL, 0.1),
(6, 'Bob', 50.45, 0.07),
(7, 'Bob', 120.5, 0.0);
CREATE TABLE payments (
    paymentid int,
    customer_name string,
    price real,
    discount real
);
INSERT INTO payments (paymentid, customer_name, price, discount)
VALUES 
(1, 'Alex', 280.12, 0.1),
(2, NULL, 35.75, NULL),
(3, 'Alex', 45.1, 0.05),
(4, 'Alex', NULL, 0.4),
(5, 'John', NULL, 0.1),
(6, 'Bob', 50.45, 0.07),
(7, 'Bob', 120.5, 0.0);
﻿
Display the Table
|
SELECT * FROM payments;
SELECT * FROM payments;
﻿
It will create a table as shown below:
The payments Table
|
+-----------+---------------+--------+----------+
| paymentid | customer_name | price  | discount |
+-----------+---------------+--------+----------+
|         2 |               |  35.75 |          |
|         4 | Alex          |        |      0.4 |
|         3 | Alex          |   45.1 |     0.05 |
|         1 | Alex          | 280.12 |      0.1 |
|         6 | Bob           |  50.45 |     0.07 |
|         5 | John          |        |      0.1 |
|         7 | Bob           |  120.5 |        0 |
+-----------+---------------+--------+----------+
+-----------+---------------+--------+----------+
| paymentid | customer_name | price  | discount |
+-----------+---------------+--------+----------+
|         2 |               |  35.75 |          |
|         4 | Alex          |        |      0.4 |
|         3 | Alex          |   45.1 |     0.05 |
|         1 | Alex          | 280.12 |      0.1 |
|         6 | Bob           |  50.45 |     0.07 |
|         5 | John          |        |      0.1 |
|         7 | Bob           |  120.5 |        0 |
+-----------+---------------+--------+----------+
﻿
#Case 1: FOR_MAX() on the whole tableFor example, let's check the price for the highest discount applied to it:
Select for_max
|
SELECT FOR_MAX(discount, price) AS for_lowest_discount
FROM payments;
SELECT FOR_MAX(discount, price) AS for_lowest_discount
FROM payments;
﻿
It will return the following output (NULL in this case):
Output
|
+---------------------+
| for_lowest_discount |
+---------------------+
|                     |
+---------------------+
+---------------------+
| for_lowest_discount |
+---------------------+
|                     |
+---------------------+
﻿
#Case 2: FOR_MAX() with GROUP BY clauseFor 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.
Select for_max
|
SELECT customer_name, FOR_MAX(price, discount) AS discount
FROM payments
GROUP BY customer_name;
SELECT customer_name, FOR_MAX(price, discount) AS discount
FROM payments
GROUP BY customer_name;
﻿
Which will give the following result:
Output
|
+---------------+----------+
| customer_name | discount |
+---------------+----------+
|               |          |
| Bob           |        0 |
| Alex          |      0.1 |
| John          |          |
+---------------+----------+
+---------------+----------+
| customer_name | discount |
+---------------+----------+
|               |          |
| Bob           |        0 |
| Alex          |      0.1 |
| John          |          |
+---------------+----------+
﻿
﻿
Updated 11 May 2023
Did this page help you?
Yes
MAX
AVG
Docs powered by archbee
TABLE OF CONTENTS
Overview
Examples
#Case 1: FOR_MAX() on the whole table
#Case 2: FOR_MAX() with GROUP BY clause