Re: SQL Question using MAX()

This question may be better for a more general newsgroup like comp.databases, as it really shouldn't depend on any Oracle features. Having said that, you could try something like:

 select area, sum_length
 from
 (select area, sum(length) as sum_length   from whatever_table
  group by area)
 where sum_length =
 (select max(sum_length)
  from
  (select sum(length) as sum_length
   from whatever_table
   group by area));

The only difference from what you want is the header for the 2nd column. Just tested this in Oracle with some similar data :-) Seems like there should be an easier way, but I can't find one right now.

Cheers,

Paul

> Dave Klinger <dklinger_at_bechtel.com> wrote:
>I have a table which has multiple records for a given area, with
>multiple areas. I want to know the area that has the maximum total
>length. Example data:
>
>area length
>---- ------
>7511 42.1
>7511 22.6
>7512 12.5
>7512 10.1
>7512 11.3
>7513 19.2
>
>Result should be:
>
>area sum(length)
>----- -----------
>7511 64.7
>
>I tried using the max() function, but when I do that I can only get back
>the maximum value, but I don't know what area that value goes to.
>Anyone have any ideas?
>
>Thanks,
>Dave

--

*    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *
* Paul J. Wagner                School   - wagner_at_cs.umn.edu               *
* Computer Science Department   Work     - wagnerp_at_uwstout.edu             *
* University of Minnesota                                                  *
*    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *