Re: SQL brainteaser..FUN!

From: <champs_at_cbr.hhcs.gov.au>
Date: 23 Feb 95 10:12:07 +1000
Message-ID: <1995Feb23.101207.1_at_cbr.hhcs.gov.au>

In article <3i2gj9$mdr_at_shark.sb.grci.com>, Walter Marek <wmarek_at_grci.com> writes:

> OK, here is the basic premise. There is a table with 2 columns:
> name char(20), age integer ... call it table T
>
> Here is some sample data :
>
> Tom,24
> Dick,40
> Harry,41
> Sally,24
> Mary,35
> Suzy,29
>
> We want to find out the AGE that occurs most frequently?
> What SQL query would return an answer of 24, not 2?

This works in Oracle, I don't know if it will anywhere else.

select age, count(*)
from t
group by age
having count(*) = (select max(count(*)) from t group by age);

>
> I am not a student trying to find someone to do their homework,
> just someone with enough knowlege of SQL to get themselves in
> trouble, but not enough to figure this one out.
>
> Please post appropriate responses to the newsgroup and mail
> a copy to wmarek_at_grci.com
>
> Have fun!

-- 
Bye for now.
Steven C.

--------------------------------------------------------------------------------
Steven Champness                   champs_at_cbr.hhcs.gov.au
Dept of Human Services & Health    Brisbane, Queensland, Australia
The opinions expressed above were found in a box of breakfast cereal

Received on Thu Feb 23 1995 - 01:12:07 CET