Cloud

for_max

The for_max() function searches for a maximum in a specific column and returns a value related to that maximum from another column.

Syntax

FOR_MAX(metric, value)

Arguments

  • metric: Must be one of the following data types: int, long, float, double, date or timestamp.

  • value: Can be any data type except text.

The for_max() function returns NULL in the following situations:

  • There are no input rows

  • The metric column contains only NULL values

  • The value corresponding to the metric maximum value is NULL

This function also returns NaN (not-a-number) if the input contains a NaN.

Examples

The following examples use a payment table that stores customer payment records, including any applied discounts:

CREATE TABLE payments (
    paymentid int,
    customer_name text,
    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);

To view the payments table content, run the query:

SELECT * FROM payments;
+-----------+---------------+--------+----------+
| 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 |
+-----------+---------------+--------+----------+

for_max() basic usage

To determine the price associated with the highest discount, run the code:

SELECT FOR_MAX(discount, price) AS for_lowest_discount
FROM payments;

The query returns the following output:

+---------------------+
| for_lowest_discount |
+---------------------+
|                     |
+---------------------+

for_max() with GROUP BY clause

This example uses a GROUP BY clause to group customers and then uses the for_max() function to get a discount for the highest price paid by each customer:

SELECT customer_name, FOR_MAX(price, discount) AS discount
FROM payments
GROUP BY customer_name;

The query returns the following output:

+---------------+----------+
| customer_name | discount |
+---------------+----------+
|               |          |
| Bob           |        0 |
| Alex          |      0.1 |
| John          |          |
+---------------+----------+