## SQL brainteaser..FUN!

### SQL brainteaser..FUN!

> Here is some sample data :
> Tom,24
>*,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 think this should work:
select age
from table
group by age
having   count(*) =  (select max(count (*))
from   table
group by  age )

--
**************************************************************

**************************************************************

### SQL brainteaser..FUN!

Quote:>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

### SQL brainteaser..FUN!

>> Here is some sample data :
>> Tom,24
>>*,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 think this should work:
>select age
>from table
>group by age
>having   count(*) =  (select max(count (*))
>                      from   table
>                   group by  age )

On ZIM's version SQL, this does not give the correct

### SQL brainteaser..FUN!

>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?

### SQL brainteaser..FUN!

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
*,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 not a student trying to find someone to do their homework,
just someone with enough knowlege of SQL to get themselves in
trouble, but not enough to figure this one out.

Please post appropriate responses to the newsgroup and mail

Have fun!

### SQL brainteaser..FUN!

> 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
>*,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...

### SQL brainteaser..FUN!

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

### SQL brainteaser..FUN!

>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 not a student trying to find someone to do their homework,
>just someone with enough knowlege of SQL to get themselves in
>trouble, but not enough to figure this one out.

>Please post appropriate responses to the newsgroup and mail

>Have fun!

CREATE VIEW
V (AGE, CNTR)
AS
SELECT
AGE, COUNT (AGE)
FROM
T
GROUP BY
AGE;

SELECT
NAME, AGE
FROM
T
WHERE
AGE =
(SELECT
AGE
FROM
V
WHERE
CNTR =
(SELECT
MAX (CNTR)
FROM
V));

### SQL brainteaser..FUN!

-> 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
->*,40
-> Harry,41
-> Sally,24
-> Mary,35
Quote:>> Suzy,29

->
-> We want to find out the AGE that occurs most frequently?
-> What SQL query would return an answer of 24, not 2?

Quote:>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

### SQL brainteaser..FUN!

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

> I am not a student trying to find someone to do their homework,
> just someone with enough knowlege of SQL to get themselves in
> trouble, but not enough to figure this one out.

> Please post appropriate responses to the newsgroup and mail

> Have fun!

--
Bye for now.
Steven C.

--------------------------------------------------------------------------------

Dept of Human Services & Health    Brisbane, Queensland, Australia
The opinions expressed above were found in a box of breakfast cereal

### SQL brainteaser..FUN!

|>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
2 rows selected
SQL>

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

### SQL brainteaser..FUN!

..
Quote:> :There is a table with 2 columns:
> >name char(20), age integer ... call it table T

> >We want to find out the AGE that occurs most frequently?
> >What SQL query would return an answer of 24, not 2?

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

Previous post include the name, if you want only the age skip the
highest level select.

Paolo Bertiglia & Daniela Rea (from Italy :)

### SQL brainteaser..FUN!

>..
>> :There is a table with 2 columns:
>> >name char(20), age integer ... call it table T

>> >We want to find out the AGE that occurs most frequently?
>> >What SQL query would return an answer of 24, not 2?

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

Hey, three selects...not bad. Can anyone manage any more? ;-)

Regards,

--
-------------------------------------------------------------------------------
Bruce Horrocks
Hampshire, England                   Object Oriented programmers only
need two methods - Create and Scoop-Up

-------------------------------------------------------------------------------

### SQL brainteaser..FUN!

> > Tom,24
> >*,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

Emeryville, CA  USA 94608-1010
Opinions may be attributed to poster, not necessarily related to Sybase.

This is a nice way to do it /*I'm Impressed!  :-) */,
but unfortunately /* :-( */ it doesn't work in Oracle.
"group function nested too deeply" or something like that.

This is the second thread I've followed in the last month
that Sybase has out-done Oracle.

"No Assembler Required!"

4. TEST