Re: SQL brainteaser..FUN!

gaverill_at_chsra4.chsra.wisc.edu (Gerard Averill) writes:

>In article <3i2gj9$mdr_at_shark.sb.grci.com> Walter Marek <wmarek@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?
>
>[snip]
>
>here's one:
>
>set rowcount 1
>select AGE from THE_TABLE group by AGE order by count(*) desc
>
>sometimes that 'set rowcount' is pretty handy, eh?
>

set rowcount is only handy in Sybase.
even the psuedo column rownum will be of limited use (cause of the group bys and order bys)

Here is a query that will work in any ansi compliant database to answer the question:

select age
from T
group by age
having count(*) >= ALL ( select count(*)

			 from T
			 group by age )

Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com Received on Sat Feb 18 1995 - 17:02:05 CET