SQL brainteaser..FUN!

SQL brainteaser..FUN!

Post by Saad Ahm » Sun, 19 Feb 1995 02:18:15




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

--
**************************************************************
*                          Saad Ahmad                        *

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

 
 
 

SQL brainteaser..FUN!

Post by Kathleen McSpurr » Sun, 19 Feb 1995 04:24:25




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
Waterloo, Ont. CANADA
    Email                      Home Page


 
 
 

SQL brainteaser..FUN!

Post by Kathleen McSpurr » Sun, 19 Feb 1995 04:27:03




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

SQL brainteaser..FUN!

Post by Gerard Averi » Sat, 18 Feb 1995 20:36:23



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

Post by Walter Mare » Sun, 19 Feb 1995 00:50:01


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!

Post by Lawrence V. Rohr » Sun, 19 Feb 1995 13:27:20



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

Post by Thomas J Ky » Mon, 20 Feb 1995 01:02:05




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

Post by (James Richard » Tue, 21 Feb 1995 22:21:48



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

Post by Dale Coop » Fri, 24 Feb 1995 06:32:18



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

Post by cha.. » Fri, 24 Feb 1995 09:12:07



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

Post by Ian Smi » Sat, 25 Feb 1995 03:24:49


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

Post by Paolo Bertigli » Sat, 25 Feb 1995 23:50:44


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

Post by Bruce Horroc » Mon, 27 Feb 1995 05:42:38





>..
>> :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!

Post by Robert Garv » Wed, 01 Mar 1995 04:55:33






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

 
 
 

1. SQL brainteaser..FUN!

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

2. <UL> nested within <OL>>

3. More fun with export...

4. TEST

5. Looking for Pre-IOUW Fun

6. Nothing after hard reset

7. Fun facts about Oracle security

8. MAME: Need Video Mode Primer/FAQ

9. 200 Mhz P6's! Sound like fun?

10. Fun & games with Designer/2000...

11. Strange behavious - inconsistencies between SQL in SQL*PLUS & SQL embedded in PL/SQL

12. in BOSTON, CDE2 / ORACLE 7.0, SQL*Plus, SQL*Forms 2.3 and 3.0, ORACLE Forms 4.0/4.5 PL-SQL, SQL*Reportwriter ,SQL * Menu

13. SQL*Menu 5.0 & SQL*Forms 3.0 Integration