1.14.1 Example: Find the customer who purchased the greatest quantity of a product
From the sales history table (SALESLIST), find the customer ID (USERID) and quantity purchased (PUR-NUM) for the customer who purchased the greatest quantity of product code P001.
- Table to search
- Specification example
-
SELECT "USERID","PUR-NUM" FROM "SALESLIST" WHERE "PUR-NUM"=(SELECT MAX("PUR-NUM") FROM "SALESLIST" WHERE "PUR-CODE"='P001')
- Retrieval results
-
- Tip
-
The subquery specified in the underlined portion searches the sales history table (SALESLIST) to find the greatest quantity purchased (9) for product code P001.
Next, it finds the customer ID (USERID) and quantity purchased (PUR-NUM) where PUR-NUM equals the greatest quantity purchased (9) identified in the subquery.