GREATEST()

GREATEST(value1, value2, …) returns the largest (maximum) value from a list of two or more arguments. Mixed numeric types are implicitly promoted to a common type following MatrixOne’s type promotion rules. Returns NULL if any argument is NULL.

Description

GREATEST() returns the largest value from the arguments provided. It compares two or more values and returns the maximum.

With the v4.1.0 update, GREATEST() implicitly promotes mixed numeric argument types rather than rejecting them. For example, comparing a BIGINT with a DOUBLE automatically promotes both to DOUBLE before comparison.

When any argument is NULL, the function returns NULL, matching MySQL behavior.

Syntax

> GREATEST(value1, value2, ...)

Arguments

Arguments

Description

value1, value2, …

Required. Two or more values to compare. Supported types include integers, floating-point numbers, decimals, strings, and temporal types.

Type Promotion Rules

When arguments have different numeric types, GREATEST() promotes them to the widest common type:

  • TINYINT + INT promotes to INT (or the wider integer type).

  • BIGINT + DOUBLE promotes to DOUBLE.

  • BIGINT + DECIMAL promotes to DECIMAL with appropriate scale.

  • Signed BIGINT + BIGINT UNSIGNED that exceeds INT64 range promotes to DECIMAL128 (lossless).

  • Mixed-scale decimals promote to the wider precision/scale.

  • All-unsigned arguments promote to the widest unsigned type.

Same-type comparisons proceed without promotion.

Examples

DROP DATABASE IF EXISTS dbfuncs;
CREATE DATABASE dbfuncs;
USE dbfuncs;

-- Basic comparison
SELECT GREATEST(1, 2.0) AS result;
-- result: 2.0

-- Mixed integer types
SELECT GREATEST(CAST(1 AS BIGINT), CAST(2.0 AS DOUBLE)) AS result;
-- result: 2.0

SELECT GREATEST(CAST(5 AS BIGINT), CAST(2.5 AS DECIMAL(10,2))) AS result;
-- result: 5.00

-- Mixed integer widths
SELECT GREATEST(CAST(1 AS TINYINT), CAST(2000 AS INT)) AS result;
-- result: 2000

-- Same-type (regression guard)
SELECT GREATEST(CAST(1 AS BIGINT), CAST(2 AS BIGINT)) AS result;
-- result: 2

SELECT GREATEST(CAST(1.0 AS DOUBLE), CAST(2.0 AS DOUBLE)) AS result;
-- result: 2.0

-- NULL handling: any NULL argument makes the result NULL
SELECT GREATEST(1, NULL, 2.0) AS result;
-- result: NULL

-- Table-driven: aggregate vs aggregate (BIGINT vs DOUBLE)
CREATE TABLE toll_transactions(id INT, gate_processing_sec DOUBLE, lanes INT);
INSERT INTO toll_transactions VALUES
  (1, 3.5, 2), (2, 7.0, 2), (3, 1.0, 4), (4, 12.5, 3);

SELECT GREATEST(COUNT(*), AVG(gate_processing_sec)) AS result FROM toll_transactions;
-- result: 6.0

-- Per-row mixed column types
CREATE TABLE mixed_num(a BIGINT, b DOUBLE, c DECIMAL(20,4));
INSERT INTO mixed_num VALUES (10, 2.5, 7.25), (3, 9.0, 4.5), (-5, -1.0, 100.0);

SELECT a, b, GREATEST(a, b) AS gab FROM mixed_num ORDER BY a;
SELECT a, b, c, GREATEST(a, b, c) AS g3 FROM mixed_num ORDER BY a;

-- Unsigned-only mixed widths
CREATE TABLE uns(u1 TINYINT UNSIGNED, u2 INT UNSIGNED, u3 BIGINT UNSIGNED);
INSERT INTO uns VALUES (1, 300, 5000000000), (200, 50, 1), (100, 70000, 9000000000);

SELECT GREATEST(u1, u2) AS g_u FROM uns ORDER BY u1;
SELECT GREATEST(u1, u2, u3) AS g3u FROM uns ORDER BY u1;

-- Signed + unsigned exceeding int64 promotes to decimal128
SELECT GREATEST(CAST(-3 AS BIGINT), CAST(9000000000000000000 AS BIGINT UNSIGNED)) AS result;
-- result: 9000000000000000000

DROP DATABASE dbfuncs;