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!

I don't think you can use # as the leading letter of a column name. But let's say zip, rte, #house are the columns, select zip, rte, #houses from your_table where (rte, #houses) in   (select rte, max(#houses) from your_table group by rte); or you can use an analytic function,
select zip, rte, max_#houses from
(select zip, rte, #houses, max(#houses) over (partition by rte) max_#houses  from your_table) where #houses=max_#houses;

They should both give you the output you wanted. The second query looks more complex. But normally it's faster.

> Thanks,
> Bill
Received on Sat Aug 24 2002 - 07:56:46 CEST