Re: select rows matching a condition in each group

On 26 Dec 2003 00:08:32 -0800, flips_at_hanmail.net (June-young Jang) wrote:

>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.

You actually don't need a GROUP BY to accomplish this

select *
from t x
where
t.cb = (select min(cb)

           from t y
           where y.ca = x.ca
          )

--
Sybrand Bakker, Senior Oracle DBA