Re: SQL query challenge!

From: Kevin McDaniel <kmcdaniel96_at_attbi.com>
Date: 23 Aug 2002 21:04:57 -0700
Message-ID: <60115ff8.0208232004.76608967_at_posting.google.com>

You could try this:

select a.zip, b.rte, b.max_houses
from test a, (select rte, max(houses) max_houses

              from test
              group by rte) b

where a.rte = b.rte
and a.houses = b.max_houses;

  ZIP RTE MAX_HOUSES
----- ---------- ----------

  102          1        200
  106          2        700

Good luck.
Kevin

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
Received on Sat Aug 24 2002 - 06:04:57 CEST