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 in a multi-modal table.

Query

Column1 

         MODE ( "Table"."Column" )

Input

Output

Table

Column : int

4

2

3

5

Result

Column1 : int

2