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

On Fri, 22 May 2009 17:48:44 +1000
"Michael Harris" <michael(dot)harris(at)ericsson(dot)com> wrote:

> Hi Experts,
>
> I want to use an aggregate function that will return the most
> commonly occurring value in a column.
>
> The column consists of VARCHAR(32) values.
>
> Is it possible to construct such an aggregate using PL/PgSql ?
>
> If I was trying to do something like this in Perl I would use a
> hash table to store the values and the number of times each was
> seen as the table was iterated, but PL/PgSql does not seem to have
> an appropriate data type for that?
>
> I don't want to use PL/Perl to avoid the overhead of starting a
> perl interpreter for that.
>
> Do I have to write the function in C maybe?

Isn't it a job for group by?

select count(*), myvalue from table group by myvalue order by
count(*) desc limit 1;

--
Ivan Sergio Borgonovo
http://www.webthatworks.it