Subquery and JOIN2

From SQLZOO
Revision as of 09:40, 30 July 2012 by Connor (Talk | contribs) (Created page with "Converting aggregate subqueries into Join functions. <div class='ht'> <div class=params>schema:scott</div> <source lang=sql class='tidy'>DROP TABLE orders</source> <source lan...")

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
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 01
WHERE 01.whn = (
 SELECT MAX(whn)
 FROM orders 02
 WHERE 01.customer = 02.customer)

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

SELECT 01.customer, 01.whn, 01.totalitems
FROM orders 01 JOIN ORDER 02 ON (01.customer = 02.customer)
GROUP BY 01.customer, 01.whn, 01.totalitems
HAVING 01.whn = MAX(02.whn)