Test subquery - Revision history

Connor: Created page with "
Here you are shown how to test two values from your subquery

to ensure that it has run correctly.

Tabl..."
2012-08-08T10:26:33Z

Created page with "<p>Here you are shown how to test two values from your subquery</p> <p>to ensure that it has run correctly.</p> <div class="ref_section"> <table class= "db_ref"> <caption>Tabl..."

New page
<p>Here you are shown how to test two values from your subquery</p>
<p>to ensure that it has run correctly.</p>
<div class="ref_section">
<table class= "db_ref">
<caption>Table 1</caption>
<tr><th align='center'>'''Customer'''</th><th>'''Item'''</th><th align='center'>'''Price'''</th></tr>
<tr><td align='left'>Brian</td><td align='left'>Table</td><td align='left'>100</td></tr>
<tr><td align='left'>Robert</td><td align='left'>Chair</td><td align='left'>20</td></tr>
<tr><td align='left'>Robert</td><td align='left'>Carpet</td><td align='left'>200</td></tr>
<tr><td align='left'>Janette</td><td align='left'>Statue</td><td align='left'>300</td></tr>
</table>
<table class= "db_ref">
<caption>Table 2</caption>
<tr><th align='center'>'''Customer'''</th><th>'''Item'''</th><th align='center'>'''Price'''</th></tr>
<tr><td align='left'>Brian</td><td align='left'>Table</td><td align='left'>100</td></tr>
<tr><td align='left'>Robert</td><td align='left'>Carpet</td><td align='left'>200</td></tr>
<tr><td align='left'>Janette</td><td align='left'>Statue</td><td align='left'>300</td></tr>
</table>
</div>
<div class='ht'>
<div class=params>schema:scott</div>
<source lang=sql class='tidy'>DROP TABLE custItem;
</source>
<source lang=sql class='setup'>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);</source>
<div>
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.
</div>
<source lang='sql' class='def'>
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)</source>

<div class="ecomm e-oracle" style="display: none"></div>
</div>

{{Hacks Ref}}