Cloud

for_min

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

Syntax

FOR_MIN(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_min() 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 minimum value is NULL

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

Examples

The following examples use a payments 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
-----------+---------------+--------+----------
         1 | Alex          | 280.12 |      0.1
         2 |               |  35.75 |
         3 | Alex          |   45.1 |     0.05
         4 | Alex          |        |      0.4
         5 | John          |        |      0.1
         6 | Bob           |  50.45 |     0.07
         7 | Bob           |  120.5 |        0
(7 rows)

for_min() basic usage

To determine the price associated with the lowest discount applied across all payments, run the query:

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

The query returns the following output:

 for_lowest_discount
---------------------
               120.5
(1 row)

for_min() with GROUP BY clause

To determine the discount associated with the lowest price paid by each customer, use the GROUP BY clause with the for_min() function:

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

This query returns the following output:

customer_name | discount
---------------+----------
 Bob           |     0.07
 Alex          |     0.05
               |
 John          |
(4 rows)