Skip to main content

MODE

Description

This function finds the most frequent elements per group. Mode can be applied on any data type. For multi-modal input, where there are several result candidates, the element with the smallest value is chosen. For elements of type STRING, the smallest value is determined by its lexicographical order.

Syntax

MODE ( table.column )

NULL handling

NULL values are ignored. If all the values of a group are NULL, the result for this group is NULL.

Examples

[1]

Find the most frequent value in a column of integers.

Query

Column1 

MODE ( "Table"."Column" )

Input

Output

Table

Column : int

2

3

3

2

3

Result

Column1 : int

3

[2]

Find the most frequent value by country.

Query

Column1 

"Table"."Country"

Column2 

MODE ( "Table"."Values" )

Input

Output

Table

Country : string

Values : int

'US'

5

'FR'

null

'DE'

2

'US'

null

'DE'

3

'DE'

3

'FR'

null

Result

Column1 : string

Column2 : int

'DE'

3

'FR'

null

'US'

5

[3]

Find the most frequent value per data type.

Query

Column1 

"values"."group"

Column2 

MODE ( "values"."integer" )

Column3 

MODE ( "values"."string" )

Column4 

MODE ( "values"."float" )

Column5 

MODE ( "values"."date" )

Input

Output

values

group : int

integer : int

string : string

float : float

date : date

1

1

'A'

1.0

Wed Jan 01 2025 00:00:00.000

2

2

'B'

2.0

Thu Jan 02 2025 00:00:00.000

2

3

'C'

3.0

Fri Jan 03 2025 00:00:00.000

2

3

'C'

3.0

Fri Jan 03 2025 00:00:00.000

3

5

'E'

5.0

Sun Jan 05 2025 00:00:00.000

3

4

'D'

4.0

Sat Jan 04 2025 00:00:00.000

4

null

null

null

null

Result

Column1 : int

Column2 : int

Column3 : string

Column4 : float

Column5 : date

1

1

'A'

1.0

Wed Jan 01 2025 00:00:00.000

2

3

'C'

3.0

Fri Jan 03 2025 00:00:00.000

3

4

'D'

4.0

Sat Jan 04 2025 00:00:00.000

4

null

null

null

null

[4]

Find the most frequent value in a multi-modal table.

Query

Column1 

MODE ( "Table"."Column" )

Input

Output

Table

Column : int

4

2

3

5

Result

Column1 : int

2