Select from another select statement : Multiple Row Subquery « Query Select « Oracle PL/SQL Tutorial






  1. Oracle PL/SQL Tutorial
  2. Query Select
  3. Multiple Row Subquery
SQL> create table t
  2  (item number,
  3   bin number,
  4   primary key (bin , item) );

Table created.

SQL>
SQL> insert into t values(2, 34353);

1 row created.

SQL> insert into t values(45,34353);

1 row created.

SQL> insert into t values(76,34353);

1 row created.

SQL> insert into t values(76,35667);

1 row created.

SQL> insert into t values(89,35667);

1 row created.

SQL> insert into t values(45,35667);

1 row created.

SQL> insert into t values(45,36767);

1 row created.

SQL>
SQL> insert into t values(46,36767);

1 row created.

SQL> insert into t values(66,36767);

1 row created.

SQL> insert into t values(86,36767);

1 row created.

SQL> insert into t values(96,36767);

1 row created.

SQL>
SQL> select * from t
  2  order by 1,2;

      ITEM        BIN
---------- ----------
         2      34353
        45      34353
        45      35667
        45      36767
        46      36767
        66      36767
        76      34353
        76      35667
        86      36767
        89      35667
        96      36767

11 rows selected.

SQL>
SQL> select bin, count(*), count(*)/cnt
  2  from (
  3         select bin, count(distinct item) over () AS cnt
  4         from t
  5         where item in (2,45,76,89) )
  6  group by bin, cnt
  7  /

       BIN   COUNT(*) COUNT(*)/CNT
---------- ---------- ------------
     35667          3          .75
     36767          1          .25
     34353          3          .75

SQL>
SQL> drop table t;

Table dropped.

SQL>
SQL>








2.38.Multiple Row Subquery
2.38.1.Writing Multiple Row Subqueries
2.38.2.Using IN with a Multiple Row Subquery
2.38.3.Update price of products that aren't selling
2.38.4.Multi-row subqueries: Show products that aren't selling
2.38.5.Uses NOT IN to check if an id is not in the list of id values in the employee table
2.38.6.Using ANY with a Multiple Row Subquery
2.38.7.Using ALL with a Multiple Row Subquery
2.38.8.Writing Multiple Column Subqueries
2.38.9.Select from another select statement
2.38.10.Subqueries That Return Multiple Results
2.38.11.First three rows from subquery