Oracle PL/SQL Tutorial/Query Select/Multi column subquery
inline view: What percentage of these items exist in each bin selected
SQL>
SQL> create table t
2 (item number,
3 bin number,
4 primary key (bin , item) );
Table created.
SQL>
SQL> insert into t values(2, 34);
1 row created.
SQL> insert into t values(45,34);
1 row created.
SQL> insert into t values(76,34);
1 row created.
SQL> insert into t values(76,35);
1 row created.
SQL> insert into t values(89,35);
1 row created.
SQL> insert into t values(45,35);
1 row created.
SQL> insert into t values(45,36);
1 row created.
SQL>
SQL> select * from t
2 order by 1,2;
2 34
45 34
45 35
45 36
76 34
76 35
89 35
7 rows selected.
SQL>
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 /
34 3 .75
35 3 .75
36 1 .25
3 rows selected.
SQL> drop table t;
Table dropped.
SQL>
Multi-column subquery
SQL>
SQL> CREATE TABLE product_order (
2 product_name VARCHAR2(25),
3 salesperson VARCHAR2(3),
4 order_date DATE,
5 quantity NUMBER(4,2)
6 );
Table created.
SQL>
SQL>
SQL> INSERT INTO product_order VALUES ("Product 1", "CA", "14-JUL-03", 1);
1 row created.
SQL> INSERT INTO product_order VALUES ("Product 2", "BB", "14-JUL-03", 75);
1 row created.
SQL> INSERT INTO product_order VALUES ("Product 3", "GA", "14-JUL-03", 2);
1 row created.
SQL> INSERT INTO product_order VALUES ("Product 4", "GA", "15-JUL-03", 8);
1 row created.
SQL> INSERT INTO product_order VALUES ("Product 5", "LB", "15-JUL-03", 20);
1 row created.
SQL> INSERT INTO product_order VALUES ("Product 6", "CA", "16-JUL-03", 5);
1 row created.
SQL> INSERT INTO product_order VALUES ("Product 7", "CA", "17-JUL-03", 1);
1 row created.
SQL>
SQL>
SQL>
SQL> SELECT * FROM product_order ORDER BY product_name, order_date;
PRODUCT_NAME SAL ORDER_DAT QUANTITY
------------------------- --- --------- ----------
Product 1 CA 14-JUL-03 1
Product 2 BB 14-JUL-03 75
Product 3 GA 14-JUL-03 2
Product 4 GA 15-JUL-03 8
Product 5 LB 15-JUL-03 20
Product 6 CA 16-JUL-03 5
Product 7 CA 17-JUL-03 1
7 rows selected.
SQL>
SQL> SELECT * FROM product_order
2 WHERE (product_name, order_date)
3 IN (SELECT product_name, MAX(order_date) FROM product_order GROUP BY product_name);
PRODUCT_NAME SAL ORDER_DAT QUANTITY
------------------------- --- --------- ----------
Product 1 CA 14-JUL-03 1
Product 2 BB 14-JUL-03 75
Product 3 GA 14-JUL-03 2
Product 4 GA 15-JUL-03 8
Product 5 LB 15-JUL-03 20
Product 6 CA 16-JUL-03 5
Product 7 CA 17-JUL-03 1
7 rows selected.
SQL>
SQL> drop table product_order;
Table dropped.
SQL>
SQL>
Subquery in from clause
SQL>
SQL> create table myTable
2 as
3 select rownum id, a.*
4 from all_objects a
5 where 1=0
6 /
Table created.
SQL>
SQL>
SQL> select *
2 from myTable myTable1, ( select owner, max(last_ddl_time) max_time from myTable group by owner ) myTable2
3 where myTable1.owner = myTable2.owner
4 /
no rows selected
SQL> drop table myTable;
Table dropped.