Re: select rows matching a condition in each group

Hello,

Please see below.

"Sybrand Bakker" <gooiditweg_at_sybrandb.demon.nl> wrote in message news:h8vnuvctuhoa4obhdlahus14kkvf5s4r5t_at_4ax.com...
> On 26 Dec 2003 00:08:32 -0800, flips_at_hanmail.net (June-young Jang)
> wrote:
>....
> 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
> )

Actually, the SQL above won't work.

The most efficient way to solve the problem would be to use analytics like this:

select * from
  (select t.*, dense_rank() over (partition by cb order by cc) rn from t) where rn = 1;

If analytics cannot be used, then another way is:

select * from
  (select x.*, (select count(distinct cc) from t where cc <= x.cc and cb=x.cb) rn from t x)
where rn = 1;

Rgds.

VC Received on Fri Dec 26 2003 - 06:53:57 CST