# SQL Challenges-1: Order ID's that executed by maximum number of salespersons

## SQL Challenges-1: Exercise-16 with Solution

From the following table, write a SQL query to find the order_id(s) that was executed by the maximum number of salespersons.
If there are, more than one order_id(s) executed by the maximum number of salespersons find all the order_id(s). Return order_id.

Input:

Table: salemast

Structure:

FieldTypeNullKeyDefaultExtra
salesperson_idint(11)YES
order_id int(11)YES

Data:

salesperson_idorder_id
50011001
50021002
50031002
50041002
50051003
50061004
50071004
50081004

Sample Solution:

SQL Code(MySQL):

``````DROP TABLE  IF EXISTS salemast;
CREATE TABLE salemast(salesperson_id int,  order_id int);
INSERT INTO salemast(salesperson_id, order_id) VALUES ('5001', '1001');
INSERT INTO salemast(salesperson_id, order_id) VALUES ('5002', '1002');
INSERT INTO salemast(salesperson_id, order_id) VALUES ('5003', '1002');
INSERT INTO salemast(salesperson_id, order_id) VALUES ('5004', '1002');
INSERT INTO salemast(salesperson_id, order_id) VALUES ('5005', '1003');
INSERT INTO salemast(salesperson_id, order_id) VALUES ('5006', '1004');
INSERT INTO salemast(salesperson_id, order_id) VALUES ('5007', '1004');
INSERT INTO salemast(salesperson_id, order_id) VALUES ('5008', '1004');
SELECT order_id
FROM salemast
GROUP BY order_id
HAVING COUNT(DISTINCT salesperson_id) = (
SELECT MAX(salesperson_count)
FROM (
SELECT COUNT(DISTINCT salesperson_id) AS salesperson_count
FROM salemast
GROUP BY order_id
) AS counts
);
```
```

Sample Output:

```order_id|
--------|
1002|
1004|
```

SQL Code Editor: