Subquery and JOIN2

From SQLZOO
Revision as of 11:23, 30 July 2012 by Connor (Talk | contribs)

Jump to: navigation, search

Converting aggregate subqueries into Join functions.

schema:scott
DROP TABLE orders
 CREATE TABLE orders(
  employee VARCHAR(40),
  whn DATE(YYYY,MM,DD),
  totalitems INTEGER );
INSERT INTO orders VALUES ('Jim',(2006,10,10), 5);
INSERT INTO orders VALUES ('Jim',(2006,10,11), 3);
INSERT INTO orders VALUES ('Jim',(2006,10,12), 1);
INSERT INTO orders VALUES ('Brian',(2006,10,10), 7);
SELECT customer, whn, totalitems
FROM orders a
WHERE a.whn = (
 SELECT MAX(whn)
 FROM orders b
 WHERE a.customer = b.customer)

To make this more efficient a HAVING clause can be used with a self join.

SELECT a.customer, a.whn, a.totalitems
FROM orders a JOIN orders b ON (a.customer = b.customer)
GROUP BY a.customer, a.whn, a.totalitems
HAVING a.whn = MAX(b.whn)
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense