davids@stsci.EDU (David Silberberg) (11/20/89)
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.
If I did a 'select A, B, C from table where B="y" ', I would get the following:
A B C
---------------------
z y 1
z y 2
z y 3
w y 1
w y 2
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.
Thanks in advance.
David Silberbergrec@indetech.com (Rick Cobb) (11/21/89)
You use the ``group by'' construct. Like so, using ORACLE version 6.0: SQL> describe abc ; /* describe is an ORACLE feature which tells you about a table or view */ Name Null? Type ------------------------------- -------- ---- A CHAR(1) B CHAR(1) C NUMBER SQL> select * from abc ; A B C - - ---------- z y 2 z y 3 w y 1 w y 2 x p 1 x z 1 x z 2 x z 3 8 records selected. SQL> select a, b, max(c) from abc group by a, b ; A B MAX(C) - - ---------- w y 2 x p 1 x z 3 z y 3 SQL> select a, b, max(c) from abc where b = 'y' group by a, b ; A B MAX(C) - - ---------- w y 2 z y 3 SQL> -- ____*_ Rick Cobb rcobb@indetech.uucp, sun!indetech!rcobb \ / / Independence Technologies An opinion is as a drop of rain. \/ / 42705 Lawrence Place That this drop fell does not mean \/ Fremont, CA 94538 my employers are all wet.
segel@quanta.eng.ohio-state.edu (Gargoyle) (11/21/89)
In article <1989Nov20.190046.12833@indetech.com> rec@indetech.UUCP (Rick Cobb) writes: > >You use the ``group by'' construct. Like so, using ORACLE version 6.0: [Example delted] >-- >____*_ Rick Cobb rcobb@indetech.uucp, sun!indetech!rcobb Rick you really didn't answer his question. Just touted your oracle knowlege . In response to the original posting, "Get me a list of the 10 oldest employees" sql statement would be something like SELECT * from employee ORDER by age DESC Which will order the list of employees by age in descending ages. Now, this is only half of the solution. To select only the first 10, you would have to use a cursor. (Or for those who use a database in which you can specify the size of the selection you need to get the appropriate syntax.) Since I do not wish to use a database language, here is roughly the solution: Build sql statement foo; Declare cursor bar for statement foo; Open cursor foobar fetch first into data_rec[1].* for i = 2 to 9 fetch next into data_rec[i].* end for {* Now display the info *} To the original poster, is this what you wanted? -Mike Segel "I only know half the answer(s)" -- -Mike Segel segel@icarus.eng.ohio-state.edu (614) 294-3350 "These opinions are my own and in no way reflect those of the University or the E E Dept.(Although there are those who probably share them!)
jwc@unify.uucp (J. William Claypool) (11/21/89)
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
dberg@cod.NOSC.MIL (David I. Berg) (11/22/89)
In article <940@cirrus.stsci.edu>, davids@stsci.EDU (David Silberberg) writes: > 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. select a, b, max(c) from table_name group by a, b -- David I. Berg (dberg@nosc.mil) GENISYS Information Systems, Inc., 4250 Pacific Hwy #118, San Diego, CA 92110 MILNET: dberg@nosc.mil UUCP: {akgua decvax dcdwest ucbvax}!sdcsvax!noscvax!dberg