T-SQL - GREATEST 関数の使い方と注意点：複数の値から最大値を取得する方法

GREATEST 関数の使い方と注意点：複数の値から最大値を取得する方法

SQL Server で開発をしていると、複数の変数やカラムに入っている値の中から、最も大きい値を取得したい場面があります。

そんな時に便利なのが、GREATEST 関数です。

GREATEST 関数は、SQL Server 2022 から使えるようになった、比較的新しい T-SQL の関数です。

この記事では、SQL Server における GREATEST 関数の基本的な使い方と注意点などをご紹介します。

※ 最小値を取得したい場合は、こちらの記事をご覧ください：LEAST 関数の使い方と注意点

GREATEST 関数の基本構文と使い方

SQL Server の GREATEST 関数は、入力引数として渡した複数の値の中で一番大きい値を返す関数です。

MySQL や PostgreSQL などでは前からありましたが、SQL Server では、SQL Server 2022 以降で使用可能になった、比較的新しい関数です。

GREATEST 関数の基本的な構文は以下の通りです。

GREATEST ( expression1 [ , ...expressionN ] )

入力引数には、「暗黙の型変換をして比較可能」なデータ型の値をカンマ区切りで指定します。

入力引数の値は、最低ひとつは必要で、最大 254 個まで指定できます。

入力引数のデータ型が違う場合、そのデータ型の中で最高の優先順位をもつデータ型に暗黙の型変換されて、比較が行われます。

比較の結果、最も大きい値を戻り値として返します。

それでは、GREATEST 関数を使って複数の値から最も大きい値を取得してみます。

SELECT GREATEST(5, 8.1, 0.3);

[実行結果]

GREATEST 関数に 5, 8.1, 0.3 の 3 つの数値を渡した結果、一番大きい 8.1 が返っています。

文字列も比較でき、その時はアルファベット順に並べて一番最後にくる文字列が返ってきます。

SELECT GREATEST('banana', 'orange', 'apple');

[実行結果]

GREATEST 関数に 'banana', 'orange', 'apple' の 3 つの文字列を渡した結果、アルファベット順で一番最後にくる 'orange' が返っています。

GREATEST 関数の NULL の扱いについて

GREATEST 関数では、NULL を含んでいても他に比較できる値があれば、NULL は無視されます。

全ての入力引数が NULL の時のみ、戻り値も NULL になります。

この動作を実際に確認してみます。

SELECT  GREATEST(5, NULL, 8.1) AS ReturnValue1,
        GREATEST(NULL, NULL, NULL) AS ReturnValue2;

[実行結果]

GREATEST 関数に 5, NULL, 8.1 の 3 つの数値を渡した結果、NULL は無視され、5 と 8.1 で大きいほうの 8.1 が戻り値として返っています。

GREATEST 関数に NULL, NULL, NULL を渡すと、戻り値も NULL になっていますね。

GREATEST 関数の戻り値のデータ型について

異なるデータ型が混在している場合は、SQL Server のデータ型の優先順位に従って、最高の優先順位のデータ型に暗黙の型変換が行われます。

GREATEST の戻り値のデータ型も、その最高の優先順位のデータ型になります。

ですので、INT 型、DECIMAL 型、VARCHAR 型の数値の入力引数があった時は、INT 型と VARCHAR 型の値は DECIMAL 型に変換され、値を比較し、一番大きい値を戻り値として DECIMAL 型として返します。

この動作を実際に確認してみます。

DECLARE @Value DECIMAL(10, 2) = 6.7;

SELECT  GREATEST(1, @value, '8.9') AS ReturnValue,
        SQL_VARIANT_PROPERTY(GREATEST(1, @value, '8.9'), 'BaseType') AS ReturnValue_BaseType,
        SQL_VARIANT_PROPERTY(GREATEST(1, @value, '8.9'), 'Precision') AS ReturnValue_Precision,
        SQL_VARIANT_PROPERTY(GREATEST(1, @value, '8.9'), 'Scale') AS ReturnValue_Scale;

[実行結果]

まず、 GREATEST 関数に 1 (INT 型)、＠Value (= 6.7, DECIMAL(10, 2) 型)、'8.9' (VARCHAR 型) の 3 つの数値を渡した結果、8.90 が返っています。

INT 型、DECIMAL 型、VARCHAR 型で DECIMAL 型が最高の優先順位のデータ型なので、1 と '8.9' は DECIMAL(10, 2) に変換されて比較が行われ、一番大きい 8.90 が戻り値になっています。

SQL_VARIANT_PROPERTY 関数を使って、戻り値のデータ型を確認したところ、BaseType が decimal、Precision が 10、Scale が 2 になっていることがわかります。

SQL Server 2022 未満の代替方法

最初にも書きましたが GREATEST 関数は SQL Server 2022 以降でしか使えません。

それより前のバージョンの SQL Server では、最も大きい値を取得するのに CASE 式を使ったり、比較したい値をテーブルなどに INSERT して MAX 関数を使う必要があります。

参考までに、FROM 句で VALUES を使ってテーブルを作り、MAX 関数を使って最大値を取得するクエリーを載せておきます。

SELECT MAX(x) AS MaxValue
FROM (VALUES (5), (8.1), (0.3)) AS Table1(x);

[実行結果]

このクエリーを実行した結果、最大値の 8.1 が取得できていますね。

FROM (VALUES ...) の構文は SQL Server 2008 以降で使用可能です。

以上、GREATEST 関数の基本的な使い方と注意点などをご説明しました。