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