Test subquery

From SQLZOO
Jump to: navigation, search

Here you are shown how to test two values from your subquery

to ensure that it has run correctly.

Table 1
CustomerItemPrice
BrianTable100
RobertChair20
RobertCarpet200
JanetteStatue300
Table 2
CustomerItemPrice
BrianTable100
RobertCarpet200
JanetteStatue300
schema:scott
DROP TABLE custItem;
CREATE TABLE custItem (
   Customer VARCHAR(20),
   Item VARCHAR(20),
   Price INT );
INSERT INTO custItem VALUES ('Brian','Table',100);
INSERT INTO custItem VALUES ('Robert','Chair',20);
INSERT INTO custItem VALUES ('Robert','Carpet',200);
INSERT INTO custItem VALUES ('Janette','Statue',300);

Suppose you have a table of customers and their orders, as shown in Table 1 and you want to produce a list of every customer and their biggest order, as shown in Table 2. This is easy enough to do with:

SELECT Customer, MAX(price)
 FROM custItem
 GROUP BY Customer

But by testing the results you can obtain the item that was purchased as well.

SELECT x.Customer, x.Item, x.Price
  FROM custItem x JOIN (
      SELECT Customer, MAX(price) AS Price
        FROM custItem
        GROUP BY Customer) y
    ON (x.Customer = y.Customer AND x.Price = y.Price)
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense