Re: SQL query challenge!

bballdestroyer_at_yahoo.com (Ken) wrote in message news:<179c0f9.0208231011.3117155a_at_posting.google.com>...
> Hello all - this seems to be an easy problem - but I'm stumped. What I
> need is a *single* SQL statement/query that can give me the zip code
> that corresponds to the greatest # of houses for each route.
> Here's an example:
> ZIP RTE #HOUSES
> 101 1 100
> 102 1 200
> 103 1 175
>
> 104 2 200
> 105 2 675
> 106 2 700
>
> etc....
> The query would return:
> ZIP RTE #HOUSES
> 102 1 200
> 106 2 700
>
> etc......
>
> Can anyone figure this out? I would be love to see a solution!
> Thanks,
> Bill

SQL> create table foo (zip number, rte number, houses number);

Table created.

SQL> insert into foo values (101, 1, 100);

1 row created.

SQL> insert into foo values (102, 1, 200);

1 row created.

SQL> insert into foo values (103, 1, 175);

1 row created.

SQL> insert into foo values (104, 2, 200);

1 row created.

SQL> insert into foo values (105, 2, 675);

1 row created.

SQL> insert into foo values (106, 2, 700);

1 row created.

SQL> commit;

Commit complete.

SQL> select *
  2 from foo a
  3 where a.houses = (select max(houses) from foo where rte = a.rte);

       ZIP RTE HOUSES
---------- ---------- ----------

       102          1        200
       106          2        700

//Rauf Sarwar Received on Sat Aug 24 2002 - 06:05:12 CEST