Re: SQL brainteaser..FUN!

In comp.databases.theory (<1995Feb23.101207.1_at_cbr.hhcs.gov.au>)   champs_at_cbr.hhcs.gov.au (champs_at_cbr.hhcs.gov.au) wrote:
> In article <3i2gj9$mdr_at_shark.sb.grci.com>,
> Walter Marek <wmarek_at_grci.com> writes:
 

> > 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);

It also works in Sybase. You should be able to eliminate the second select using this form:

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

Also, in general it is not good practice with the Sybase SQL Server to set the rowcount to one and then do a select with order by to get a row with the largest or smallest value of a column, a technique suggested in another posting. This is unnecessarily expensive.

• Robert robert.garvey_at_sybase.com Sybase, Inc 6475 Christie Ave Emeryville, CA USA 94608-1010 Opinions may be attributed to poster, not necessarily related to Sybase.