Re: select rows matching a condition in each group

From: VC <boston103_at_hotmail.com>
Date: Fri, 26 Dec 2003 16:20:17 GMT
Message-ID: <5dZGb.668395$Fm2.580852@attbi_s04>

Hello Sybrand,

Please see below.

"Sybrand Bakker" <gooiditweg_at_sybrandb.demon.nl> wrote in message news:lilouvgij3pbqe9o8kcft6idfrav37phe7_at_4ax.com...
> On Fri, 26 Dec 2003 12:53:57 GMT, "VC" <boston103_at_hotmail.com> 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.
>
> Are you sure? Did you try it? I doubt it. Worked for me numerous
> times.

If you insist...

Given:

insert into t values(  1 ,'G1', 10  ,'A');
insert into t values(  2 ,'G2', 20  ,'Q');
insert into t values(  3 ,'G1', 50  ,'Z');
insert into t values(  4 ,'G1', 45  ,'T');
insert into t values(  5 ,'G2', 90  ,'H');
insert into t values(  6 ,'G3', 90  ,'P');

The desired result should be:

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

In sqlplus:

SQL> create Table T(CA int, CB varchar2(2), CC int, CD varchar2(2));

Table created.

SQL>
SQL> insert into t values( 1 ,'G1', 10 ,'A');

1 row created.

SQL> insert into t values( 2 ,'G2', 20 ,'Q');

1 row created.

SQL> insert into t values( 3 ,'G1', 50 ,'Z');

1 row created.

SQL> insert into t values( 4 ,'G1', 45 ,'T');

1 row created.

SQL> insert into t values( 5 ,'G2', 90 ,'H');

1 row created.

SQL> insert into t values( 6 ,'G3', 90 ,'P');

1 row created.

---

Now, your suggestion gives this:

SQL> select *
  2 from t x
  3 where
  4 t.cb = (select min(cb)

  5             from t y
  6             where y.ca = x.ca
  7            )

  8
SQL> /
t.cb = (select min(cb)
*
ERROR at line 4:
ORA-00904: "T"."CB": invalid identifier

---

Having corrected the syntax, we'll get this:

SQL> select *
  2 from t x
  3 where
  4 x.cb = (select min(cb)

  5             from t y
  6             where y.ca = x.ca
  7            )

  8
SQL> /         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

6 rows selected.

---

Obviously, not what the poster wanted.

What you probably meant was:

select *
from t x
where
cc = (select min(cc)

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

However, it's still less performant (because of the correlated subquery) than the analytical version below:

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

        CA CB CC CD RN
---------- -- ---------- -- ----------

         1 G1         10 A           1
         2 G2         20 Q           1
         6 G3         90 P           1

Rgds.

VC Received on Fri Dec 26 2003 - 10:20:17 CST