SQL Challenges-1: Find highest sale amount that appears distinctly

Last update on December 20 2024 11:39:43 (UTC/GMT +8 hours)

From the following table write a query in SQL to find the highest sale among salespersons that appears only once. Return salesperson ID and sale amount

Input:

Table: sales

Structure:

Field	Type	Null	Key
TRANSACTION_ID	int(5)	NO	PRI
SALESMAN_ID	int(4)	NO
SALE_AMOUNT	decimal(8,2)	YES

Data:

TRANSACTION_ID	SALESMAN_ID	SALE_AMOUNT
501	18	5200.00
502	50	5566.00
503	38	8400.00
504	43	8400.00
505	11	9000.00
506	42	12200.00
507	13	7000.00
508	33	6000.00
509	41	8200.00
510	11	4500.00
511	51	10000.00
512	29	9500.00
513	59	6500.00
514	38	7800.00
515	58	9800.00
516	60	12000.00
517	58	14000.00
518	23	12200.00
519	34	5480
520	35	8129
521	49	9323
522	46	8200.00
523	47	9990.00
524	42	14000.00
525	44	7890.00
526	47	5990.00
527	21	7770.00
528	57	6645.00
529	56	5125.00
530	25	10990.00

Sample Solution:

SQL Code(MySQL):

DROP TABLE IF EXISTS sales;
CREATE TABLE sales (
TRANSACTION_ID INTEGER(5) NOT NULL,
SALESMAN_ID   INTEGER(4) NOT NULL,
SALE_AMOUNT  decimal(8,2),
PRIMARY KEY (TRANSACTION_ID)
);

INSERT INTO sales VALUES(501,18,5200.00);
INSERT INTO sales VALUES(502,50,5566.00);
INSERT INTO sales VALUES(503,38,8400.00);
INSERT INTO sales VALUES(504,43,8400.00);
INSERT INTO sales VALUES(505,11,9000.00);
INSERT INTO sales VALUES (506,42,12200.00);
INSERT INTO sales VALUES(507,13,7000.00);
INSERT INTO sales VALUES(508,33,6000.00);
INSERT INTO sales VALUES(509,41,8200.00);
INSERT INTO sales VALUES(510,11,4500.00);
INSERT INTO sales VALUES (511,51,10000.00);
INSERT INTO sales VALUES(512,29,9500.00);
INSERT INTO sales VALUES(513,59,6500.00);
INSERT INTO sales VALUES(514,38,7800.00);
INSERT INTO sales VALUES(515,58,9800.00);
INSERT INTO sales VALUES (516,60,12000.00);
INSERT INTO sales VALUES(517,58,14000.00);
INSERT INTO sales VALUES(518,23,12200.00);
INSERT INTO sales VALUES(519,34,5480.00);
INSERT INTO sales VALUES(520,35,8129.00);
INSERT INTO sales VALUES (521,49,9323.00);
INSERT INTO sales VALUES(522,46,8200.00);
INSERT INTO sales VALUES(523,47,9990.00);
INSERT INTO sales VALUES(524,42,14000.00);
INSERT INTO sales VALUES(525,44,7890.00);
INSERT INTO sales VALUES (526,47,5990.00);
INSERT INTO sales VALUES(527,21,7770.00);
INSERT INTO sales VALUES(528,57,6645.00);
INSERT INTO sales VALUES(529,56,5125.00);
INSERT INTO sales VALUES(530,25,10990.00);



SELECT sale_amount
FROM sales
GROUP BY sale_amount
HAVING count(*) = 1
ORDER BY sale_amount DESC
LIMIT 1;

Sample Output:

sale_amount|
-----------+
   12000.00|

SQL Code Editor:

Contribute your code and comments through Disqus.

Previous: Find departments with 6 or more employees.
Next: Find the highest daily total order for an item.