Test subquery

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

to ensure that it has run correctly.

Table 1
Customer	Item	Price
Brian	Table	100
Robert	Chair	20
Robert	Carpet	200
Janette	Statue	300

Table 2
Customer	Item	Price
Brian	Table	100
Robert	Carpet	200
Janette	Statue	300

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)

Test subquery

Navigation menu

Search