Path: utzoo!attcan!uunet!cs.utexas.edu!tut.cis.ohio-state.edu!ucbvax!ucdavis!csusac!unify!jwc
From: jwc@unify.uucp (J. William Claypool)
Newsgroups: comp.databases
Subject: Re: How do you do this query?
Keywords: SQL, query
Message-ID: <1989Nov21.051631.16256@unify.uucp>
Date: 21 Nov 89 05:16:31 GMT
References: <940@cirrus.stsci.edu>
Reply-To: jwc@unify.UUCP (J. William Claypool)
Organization: Unify Corporation, Sacramento, CA, USA
Lines: 62

In article <940@cirrus.stsci.edu> davids@stsci.EDU (David Silberberg) writes:
>How do you do the following query in SQL?
>
>Imagine a table of the following:
>
>	A	B	C
>      ---------------------
>	z	y	1
>	z	y	2
>	z	y	3
>	w	y	1
>	w	y	2
>	x	p	1
>	x	z	1
>	x	z	2
>	x	z	3
>	etc.
...
>What I really want is a select that would get only the line with the
>maximum value of C for each unique combination of A, B.  It would
>produce the following:
>
>	A	B	C
>      ---------------------
>	z	y	3
>	w	y	2
>
>Remember, only SQL standard queries allowed. 

Excuse the caps.  You did say standard ;-)

SELECT A, B, MAX(C)
FROM T
GROUP BY A, B;

Would produce:

	A	B	C
      ---------------------
	w	y	2
	x	p	1
	x	z	3
	z	y	3
	...

SELECT A, B, MAX(C)
FROM T
WHERE B = 'y'
GROUP BY A;

Would produce:

	A	B	C
      ---------------------
	w	y	2
	z	y	3
	...
-- 

Bill Claypool    W. (916) 920-9092 |I know what I know if you know what I mean
jwc@unify.UUCP   H. (916) 381-4205 |------------------------------------------
    ...!{csusac,pyramid}!unify!jwc |  SCCA SFR Solo II   74 es  1984 CRX 1.5