CODEWITHSUNDEEP
postgresql
Guilherme
Guilherme

Reputation: 1835

Tiebreaker criterion of the mode() in postgres

When using the mode() aggregation function, which tiebreaker criterion does the method use?

 select  mode() within group (order by my_field) FROM my_table

I couldn't find any documentation related to that

What happens if the column has an equal amount of occurrence of the values

select my_field, count(*) FROM my_table group by 1
status count
4096 24
4098 24

In this example above, I am getting 4096, but I would like to confirm if it actually gets the lowest result, or if this is happening for another reason

UPDATE:

I still don't know how to fix this so that it's not an arbitrary choice, for now I'm using another order by

select  mode() within group (order by my_field) FROM my_table order by my_field

Upvotes: 0

Views: 316

Answers (1)

dfundako
dfundako

Reputation: 8324

Per the docs, it is arbitrary:

mode () WITHIN GROUP ( ORDER BY anyelement ) → anyelement

Computes the mode, the most frequent value of the aggregated argument (arbitrarily choosing the first one if there are multiple equally-frequent values). The aggregated argument must be of a sortable type.

https://www.postgresql.org/docs/current/functions-aggregate.html#FUNCTIONS-ORDEREDSET-TABLE

Upvotes: 1

Related Questions