Applies to:  CELONIS 4.7 

Description

The LEAST function returns the least 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 LEAST function requires at least two expressions.

Unicode Support

For the STRING type, LEAST only supports the ASCII range of characters, and not the full Unicode character set.

SYNTAX

LEAST ( table.column1 , ... , table.columnN )

NULL handling

If all values are NULL, then LEAST 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 LEAST function might be a good alternative to CASE WHEN statements for its reduced syntax.
  • The GREATEST function is the reverse function of LEAST.

Examples


[1] LEAST with four integer columns as arguments with some null values at different positions:

Query
Column1
LEAST ( "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
1
5
10
15
17
24
null
29



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

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



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

Query
Column1
LEAST ( "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] LEAST with four float columns as arguments with some null values at different positions:

Query
Column1
LEAST ( "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
1.0
5.0
10.0
15.0
17.0
24.0
null
29.0



[5] LEAST with four string columns as arguments with some null values at different positions:

Query
Column1
LEAST ( "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
'Text1'
'Text5'
'Text10'
'Text15'
'Text17'
'Text24'
null
'Text29'



[6] LEAST with four date columns as arguments with some null values at different positions:

Query
Column1
LEAST ( "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
Sat Mar 28 2020 01:49:00.000
Sun Mar 01 2020 04:15:00.000
Sat Feb 01 2020 13:01:00.000
Sat Feb 01 2020 13:01:00.000
Sun Mar 01 2020 13:01:00.000
Sun Apr 12 2020 13:01:00.000
null
Sat Feb 01 2020 03:10:00.000


See also:

  • No labels