Re: How to get most frequent and least frequent values in a column?

Steve Atkins wrote:
> On Mon, Sep 20, 2004 at 02:27:41PM +0000, Matthew Wilson wrote:
>
>>I'm a noob SQL user, crossing over from SAS.  I have a table with about
>>200k rows and one of the columns is empssn, which holds the employee
>>social security number.  The same empssn may appear in lots of different
>>rows.  I want to get a list of the 40 top empssns, sorted by the number
>>of times they appear in the table. I also want a list of the very rarest
>>empssns (ones that only appear once or twice).
>>
>>Can anyone help me with this?  BTW, this isn't a homework problem.
>
>
>   select empssn, count(*) from table
>          group by empssn
>          order by count(*) desc limit 40;
>
> and
>
>   select empssn, count(*) from table
>          group by empssn
>          having count(*) < 3;
>
> may be close to what you're looking for.
>
> Cheers,
>   Steve
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match
>


to get the 10 rarest empssns, it should be fine to do:

SELECT empssn, count(*) from table
        GROUP BY empssn
        ORDER BY count(*) ASC LIMIT 10;


the only thing here change from the previous post is the ASC (ascending)
or DESC (descending) sort order.
the having count(*) < 3 is good but will only return rows if there are
empssn that only occur less then  3 times in the table.
however with a table with 200 000 records there is a chance/risk that
not any empssn occurs less then 3 times, in such a case the query with
the HAVING clause will return zero rows whereas the LIMIT 10 still will
give the 10 least frequent.