Applies to: CELONIS 4.7
Description
The GREATEST function returns the greatest element that is not NULL from a set of values.
It supports all PQL types (including table columns) as input. All arguments must be of the same data type. The data type of the result is the same as the data type of the input columns or values.
The GREATEST function requires at least two expressions.
Unicode Support
For the STRING type, GREATEST only supports the ASCII range of characters, and not the full Unicode character set.
SYNTAX
GREATEST ( table.column1 , ... , table.columnN )
NULL handling
If all values are NULL, then GREATEST will also return NULL.
Tips
- In the argument list, you can mix columns with constants. This allows you to define a constant default value for the case.
- The GREATEST function might be a good alternative to CASE WHEN statements for its reduced syntax.
- The LEAST function is the reverse function of GREATEST.
Examples
[1] GREATEST with four integer columns as arguments with some null values at different positions:
Query
Column1
GREATEST ( "Table1"."Column1" , "Table1"."Column2" , "Table1"."Column3" , "Table1"."Column4" )
[3] GREATEST with four integer columns as arguments containing only null values:
Query
Column1
GREATEST ( "Table1"."Column1" , "Table1"."Column2" , "Table1"."Column3" , "Table1"."Column4" )
[4] GREATEST with four float columns as arguments with some null values at different positions:
Query
Column1
GREATEST ( "Table1"."Column1" , "Table1"."Column2" , "Table1"."Column3" , "Table1"."Column4" )
[5] GREATEST with four string columns as arguments with some null values at different positions:
Query
Column1
GREATEST ( "Table1"."Column1" , "Table1"."Column2" , "Table1"."Column3" , "Table1"."Column4" )
Output
Result
Column1 : STRING |
---|
'Text4' |
'Text8' |
'Text12' |
'Text16' |
'Text20' |
'Text24' |
null |
'Text32' |
[6] GREATEST with four date columns as arguments with some null values at different positions:
Query
Column1
GREATEST ( "Table1"."Column1" , "Table1"."Column2" , "Table1"."Column3" , "Table1"."Column4" )
Input
Table1
Column1 : DATE | Column2 : DATE | Column3 : DATE | Column4 : DATE |
---|---|---|---|
Thu Apr 02 2020 13:01:00.000 | Fri Apr 10 2020 08:01:00.000 | Sat Mar 28 2020 01:49:00.000 | Sun Apr 05 2020 13:44:00.000 |
null | Sun Mar 01 2020 04:15:00.000 | Tue May 12 2020 09:59:00.000 | Mon Apr 27 2020 13:01:00.000 |
null | Sat Feb 01 2020 13:01:00.000 | null | Sat Feb 01 2020 13:04:00.000 |
null | null | Tue Feb 04 2020 13:01:00.000 | Sat Feb 01 2020 13:01:00.000 |
Sun Mar 01 2020 13:01:00.000 | null | Wed Mar 11 2020 14:47:00.000 | Wed Mar 11 2020 14:48:00.000 |
null | null | null | Sun Apr 12 2020 13:01:00.000 |
null | null | null | null |
Tue Mar 03 2020 13:10:00.000 | Wed Mar 04 2020 09:01:00.000 | Sat Feb 01 2020 03:10:00.000 | Thu Apr 09 2020 12:10:00.000 |
Output
Result
Column1 : DATE |
---|
Fri Apr 10 2020 08:01:00.000 |
Tue May 12 2020 09:59:00.000 |
Sat Feb 01 2020 13:04:00.000 |
Tue Feb 04 2020 13:01:00.000 |
Wed Mar 11 2020 14:48:00.000 |
Sun Apr 12 2020 13:01:00.000 |
null |
Thu Apr 09 2020 12:10:00.000 |