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" )
Input
Table1
Column1 : INTColumn2 : INTColumn3 : INTColumn4 : INT
1
2
3
4
5
null
7
8
null
10
11
12
null
null
15
16
17
18
19
20
null
null
null
24
null
null
null
null
29
30
31
32
Output
Result
Column1 : INT
4
8
12
16
20
24
null
32



[2] GREATEST with two columns and a constant as arguments:

Query
Column1
GREATEST ( "Table1"."Column1" , "Table1"."Column2" , 7 )
Input
Table1
Column1 : INTColumn2 : INT
null
1
2
3
null
null
4
5
6
null
Output
Result
Column1 : INT
7
7
7
7
7



[3] GREATEST with four integer columns as arguments containing only null values:

Query
Column1
GREATEST ( "Table1"."Column1" , "Table1"."Column2" , "Table1"."Column3" , "Table1"."Column4" )
Input
Table1
Column1 : INTColumn2 : INTColumn3 : INTColumn4 : INT
null
null
null
null
null
null
null
null
null
null
null
null
Output
Result
Column1 : INT
null
null
null



[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" )
Input
Table1
Column1 : FLOATColumn2 : FLOATColumn3 : FLOATColumn4 : FLOAT
1.0
2.0
3.0
4.0
5.0
null
7.0
8.0
null
10.0
11.0
12.0
null
null
15.0
16.0
17.0
18.0
19.0
20.0
null
null
null
24.0
null
null
null
null
29.0
30.0
31.0
32.0
Output
Result
Column1 : FLOAT
4.0
8.0
12.0
16.0
20.0
24.0
null
32.0



[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" )
Input
Table1
Column1 : STRINGColumn2 : STRINGColumn3 : STRINGColumn4 : STRING
'Text1''Text2''Text3''Text4'
'Text5'null'Text7''Text8'
null'Text10''Text11''Text12'
nullnull'Text15''Text16'
'Text17''Text18''Text19''Text20'
nullnullnull'Text24'
nullnullnullnull
'Text29''Text30''Text31''Text32'
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 : DATEColumn2 : DATEColumn3 : DATEColumn4 : 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


See also:

  • No labels