Mega Code Archive
Multi-column subquery
<![CDATA[
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> 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> 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>
]]>