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

SQL Challenges-1: Exercise-62 with Solution

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:

Data:

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.