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.

To Page Top