Re: SQL brainteaser..FUN!

From: Kathleen McSpurren <kmcspurr_at_watarts.uwaterloo.ca>
Date: Fri, 17 Feb 1995 19:24:25 GMT
Message-ID: <D45sKr.IE_at_watserv2.uwaterloo.ca>

In article <3i2gj9$mdr_at_shark.sb.grci.com>, Walter Marek <wmarek_at_grci.com> wrote:
>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?
>

I am posting this from my wife's account since my news server is down...

I tested this out with a bunch of data and I am fairly secure that it will always give you the result  you want... I tested this out on ZIM's SQL implementation

select age $count(age) from T group by age having $count(age) = \ (select $count(age) from T group by age order by $count(age) desc)

The logic behind it is that the sub querry finds a table in descending order of counts, in effect giving you the  maximum count. Then I just look for the Intersection.

        ---Mike

---

Mike Tancsa, 2nd Year CP/A Student, Conestoga College Waterloo, Ont. CANADA

    Email                      Home Page

mdtancsa_at_sentex.net http://www.sentex.net/~mdtancsa/ Received on Fri Feb 17 1995 - 20:24:25 CET