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

# 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:

FieldTypeNullKeyDefaultExtra
TRANSACTION_IDint(5)NOPRI
SALESMAN_IDint(4)NO
SALE_AMOUNTdecimal(8,2)YES

Data:

TRANSACTION_IDSALESMAN_IDSALE_AMOUNT
501185200.00
502505566.00
503388400.00
504438400.00
505119000.00
5064212200.00
507137000.00
508336000.00
509418200.00
510114500.00
5115110000.00
512299500.00
513596500.00
514387800.00
515589800.00
5166012000.00
5175814000.00
5182312200.00
519345480
520358129
521499323
522468200.00
523479990.00
5244214000.00
525447890.00
526475990.00
527217770.00
528576645.00
529565125.00
5302510990.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: