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

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