Re: Aggregate Function to return most common value for a column

Hi Scott,

Brilliant, that's exactly what I wanted.

I guess the only thing that worries me is if the table being aggregated
is very large, I assume this solution will use a lot of memory - since
it creates an array containing all of the values in the target
expression - but I suspect in my application that won't be a problem.

Thanks again,

Regards // Mike

-----Original Message-----
From: artacus(at)comcast(dot)net [mailto:artacus(at)comcast(dot)net]
Sent: Saturday, 23 May 2009 1:23 AM
To: Michael Harris
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Aggregate Function to return most common value
for a column

> I want to use an aggregate function that will return the most commonly

> occurring value in a column.

It's actually dead simple in Postgres. No C either. You just need to
create an aggregate function.
I wrote a most() aggregate a while back that does exactly what you are
asking for. Here, I'll add it to my blog...

http://scottrbailey.wordpress.com/2009/05/22/postgres-adding-custom-aggr
egates-most/

Scott Bailey