Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> select rows matching a condition in each group

select rows matching a condition in each group

From: June-young Jang <flips_at_hanmail.net>
Date: 26 Dec 2003 00:08:32 -0800
Message-ID: <cbfa2ee1.0312260008.5d5d50ed@posting.google.com>


Hello..
I need a *simple* and *efficient* SQL query like this.
(I am working on Oracle 9i)

Example) Table T.

 CA CB CC CD
--- --- --- ---
  1 G1 10 A
  2 G2 20 Q
  3 G1 50 Z
  4 G1 45 T
  5 G2 90 H
  6 G3 90 P

Result that I want)

 CA CB CC CD
--- --- --- ---
  1 G1 10 A
  2 G2 20 Q
  6 G3 90 P

That is,,,

  1. grouping by column CB
    (there are 3 groups in table T)
  2. selecting rows satisfing a condition from each groups.
    (the condition in above example is
    smallest CC value)

so, the results are

the row whose CC = 10 in group G1,
the row whose CC = 20 in group G2,
the row whose CC = 90 in group G3

Note that I also need other columns(CA, CD) in the result. Received on Fri Dec 26 2003 - 02:08:32 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US