﻿ SQL Challenges-1: Highest purchase for each customer - w3resource

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:

FieldTypeNullKeyDefaultExtra
customer_idint(11)NO
item_codeint(11)NO
purch_qtyint(11)NO

Data:

customer_iditem_codepurch_qty
10150425
10150350
10250240
10250325
10250145
10350530
10350325
10450540
10150225
10250440
10250550
10350225
10450440
10350135

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:

﻿

SQL: Tips of the Day

How to select the nth row in a SQL database table?

Basically, PostgreSQL and MySQL supports the non-standard:

```SELECT...
LIMIT y OFFSET x
```

Oracle, DB2 and MSSQL supports the standard windowing functions:

```SELECT * FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
columns
FROM tablename
) AS foo
WHERE rownumber <= n
```

Database: SQL

Ref: https://bit.ly/3zPxcD8