Here you are shown how to test two values from your subquery
to ensure that it has run correctly.
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)