Re: SQL brainteaser..FUN!

From: Dale Cooper <cooper_at_seismo.CSS.GOV>
Date: 22 Feb 95 21:32:18 GMT
Message-ID: <3igah2$2n7_at_seismo.CSS.GOV>

In article <lrohrer-1702952027200001_at_lrohrer.earthlink.net> lrohrer_at_earthlink.net (Lawrence V. Rohrer) writes: >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?
 

>select age 'Most frequent age'
>  from T
> group by age 
> having count(*) = max(count(*))
>
>I believe this will work but it hasn't been tested...

Gotta test em first! ;)

Try:

SQL> select age from junk
  2 group by age
  3 having count(*) = (select max(count(age)) from junk group by age);

     
       AGE
----------
        33

If there are ties, it prints each one.

Dale Cooper, DBA
Center for Monitoring Research
Arlington, VA Received on Wed Feb 22 1995 - 22:32:18 CET