SQL Challenges-1: Highest purchase for each customer

SQL Challenges-1: Exercise-38 with Solution

From the following table write a SQL query to find the highest purchase with its corresponding item for each customer. In case of a same quantity purchase find the item code which is smallest.
The output must be sorted by increasing of customer_id. Return customer ID,lowest item code and purchase quantity.

Input:

Table: purchase

Structure:

Data:

Sample Solution:

SQL Code(MySQL):

CREATE TABLE purchase (customer_id int not null, item_code int not null, purch_qty int not null);
INSERT INTO purchase VALUES (101,504,25 );
INSERT INTO purchase VALUES (101,503,50 );
INSERT INTO purchase VALUES (102,502,40 );
INSERT INTO purchase VALUES (102,503,25 );
INSERT INTO purchase VALUES (102,501,45 );
INSERT INTO purchase VALUES (103,505,30 );
INSERT INTO purchase VALUES (103,503,25 );
INSERT INTO purchase VALUES (104,505,40 );
INSERT INTO purchase VALUES (101,502,25 );
INSERT INTO purchase VALUES (102,504,40 );
INSERT INTO purchase VALUES (102,505,50 );
INSERT INTO purchase VALUES (103,502,25 );
INSERT INTO purchase VALUES (104,504,40 );
INSERT INTO purchase VALUES (103,501,35 );

SELECT customer_id, min(item_code) as 'lowest item code', purch_qty
FROM purchase
WHERE (customer_id, purch_qty) IN (SELECT customer_id, max(purch_qty) 
FROM purchase
GROUP BY customer_id)
GROUP BY customer_id, purch_qty
ORDER BY customer_id;

Sample Output:

customer_id|lowest item code|purch_qty|
-----------|----------------|---------|
        101|             503|       50|
        102|             505|       50|
        103|             501|       35|
        104|             504|       40|

SQL Code Editor:

Contribute your code and comments through Disqus.

Previous: Sale history of items for a particular period.
Next: Find all the writers who rated at least one of their own topic.