Difference between revisions of "Subquery and JOIN2"

From SQLZOO

Jump to: navigation, search

Revision as of 10:25, 30 July 2012

Converting aggregate subqueries into Join functions.

schema:scott

DROP TABLE orders

 CREATE TABLE orders(
  customer VARCHAR(40),
  whn VARCHAR(40),
  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)

Retrieved from "http://sqlzoo.net/w/index.php?title=Subquery_and_JOIN2&oldid=2792"

Personal tools

Reference

Toolbox

Google AdSense