Skip to content

topn()

Get the top N most common values from a space-saving aggregate

Since 1.16.0

Get the top N most common values from a space-saving aggregate. The space-saving aggregate can be created from either freq_agg or mcv_agg.

Get the 20 most frequent zip_codes from an employees table:

SELECT topn(mcv_agg(20, zip_code)) FROM employees;

The syntax is:

topn (
agg SpaceSavingAggregate
[, n INTEGER]
) RETURNS SETOF AnyElement
NameTypeDefaultRequiredDescription
aggSpaceSavingAggregate-✔A space-saving aggregate created using either freq_agg or mcv_agg
nINTEGER-The number of values to return. Required for aggregates created with freq_agg. For aggregates created with mcv_agg, defaults to the aggregate's count; a higher n is an error. A freq_agg aggregate returns fewer than n values if fewer values reach its frequency cutoff. An mcv_agg aggregate returns fewer than n values if it holds fewer distinct values, and raises an error if the data is not skewed enough to support n values.
ColumnTypeDescription
topnSETOF AnyElementThe N most-frequent values in the aggregate