Re: SQL brainteaser..FUN!

In article <3i2gj9$mdr_at_shark.sb.grci.com>, Walter Marek <wmarek_at_grci.com> writes:
|>We want to find out the AGE that occurs most frequently?
|>What SQL query would return an answer of 24, not 2?

This was fun. How many solutions used ANSI/ISO SQL? I added an extra row so that more than one age matched.

SQL> insert into T values ('Tom', 24);
1 row inserted
SQL> insert into T values ('Dick', 40);
1 row inserted
SQL> insert into T values ('Harry', 41); 1 row inserted
SQL> insert into T values ('Sally', 24); 1 row inserted
SQL> insert into T values ('Mary', 35);
1 row inserted
SQL> insert into T values ('Mary2', 35); -- added for interest 1 row inserted
SQL> insert into T values ('Suzy',29);
1 row inserted

SQL>
SQL> -- Find age that occurs most frequently
SQL> -- Works for Oracle Rdb (formerly DEC Rdb) using ANSI/ISO SQL-92 syntax
SQL>
SQL> -- Uses SQL-92 derived table syntax and nested subquery
SQL> select age, count(*)
cont> from T
cont> group by age
cont> having count(*) =
cont>   (select max(age_count)
cont>    from (select age, count(*) as age_count
cont>          from T
cont>          group by age) as AGE_TABLE (age, age_count));
         AGE
          24             2
          35             2

-- 
Ian Smith
Rdb Engineering Group
(Standard disclaimer: These opinions are mine and in no way represent a
		      commitment or opinion of my employer)