Re: SQL Question using MAX()

From: Matthias Gresz <GreMa_at_t-online.de>
Date: 20 Nov 1998 07:35:20 GMT
Message-ID: <73363o$1e$1@news01.btx.dtag.de>

Hi,

according to Jurij Modic's solution to the TOP_X-problem you could also do:

SELECT /*+ RULE */
        area
FROM

	(SELECT
		area,
		sum(l_length) as lsum
 	FROM
		Your_table
	GROUP BY
		area
	)t, 
	dual
WHERE 
	-1*t.lsum = DECODE(dual.dummy(+),'X',NULL,NULL)
AND 
	rownum = 1

ORDER BY t.lsum DESC;

Dave Klinger schrieb:
>
> 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

--
Matthias.Gresz_at_Privat.Post.DE

Always log on the bright side of life.
http://www.stone-dead.asn.au/movies/life-of-brian/brian-31.htm Received on Fri Nov 20 1998 - 01:35:20 CST