Free Trial

Safari Books Online is a digital library providing on-demand subscription access to thousands of learning resources.

  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

10. Reporting > Test Two Values from a Subquery

Test Two Values from a Subquery

Testing against a single-column subquery is straightforward. Testing against two columns is harder, but there are still plenty of options.

Suppose you have a table of customers and their orders, as shown in Table 10-15.

Table 10-15. The custItem table
CustomerItemPrice
BrianTable100
RobertChair20
RobertCarpet200
JanetteStatue300

You want to produce a list of every customer and their biggest order, as shown in Table 10-16.

Table 10-16. Biggest order by customer
CustomerItemPrice
BrianTable100
RobertCarpet200
JanetteStatue300

Spotting the highest price per customer is easy. You can use:

mysql> SELECT Customer, MAX(Price)
    ->   FROM custItem
    ->  GROUP BY Customer;
+----------+------------+
| Customer | MAX(Price) |
+----------+------------+
| Brian    |        100 |
| Janette  |        300 |
| Robert   |        200 |
+----------+------------+

  

You are currently reading a PREVIEW of this book.

                                                                                                                    

Get instant access to over $1 million worth of books and videos.

  

Start a Free Trial


  
  • Safari Books Online
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint