8/10 每日一題(MYSQL 尋找不重複的最大值,不存在時要返回null)

 Table: MyNumbers

+-------------+------+
| Column Name | Type |
+-------------+------+
| num         | int  |
+-------------+------+
This table may contain duplicates (In other words, there is no primary key for this table in SQL).
Each row of this table contains an integer.

 

A single number is a number that appeared only once in the MyNumbers table.

Find the largest single number. If there is no single number, report null.

The result format is in the following example.

 

Example 1:

Input: 
MyNumbers table:
+-----+
| num |
+-----+
| 8   |
| 8   |
| 3   |
| 3   |
| 1   |
| 4   |
| 5   |
| 6   |
+-----+
Output: 
+-----+
| num |
+-----+
| 6   |
+-----+
Explanation: The single numbers are 1, 4, 5, and 6.
Since 6 is the largest single number, we return it.

Example 2:

Input: 
MyNumbers table:
+-----+
| num |
+-----+
| 8   |
| 8   |
| 7   |
| 7   |
| 3   |
| 3   |
| 3   |
+-----+
Output: 
+------+
| num  |
+------+
| null |
+------+
Explanation: There are no single numbers in the input table so we return null.

# Write your MySQL query statement below

SELECT (

SELECT num

FROM MyNumbers

GROUP BY num

HAVING COUNT(num)=1

order by num DESC

LIMIT 1

) AS num

#這邊使用子查詢 是因為 當最大的唯一值不存在時  找一個不存在的值 他會回傳null

-------------參考解答

MySQL

Approach : Using Count and subqueries

Query1

SELECT MAX(num) AS num FROM (SELECT num FROM MyNumbers GROUP BY num HAVING COUNT(num) = 1 ) NEW;

Query2

SELECT MAX(num) AS num  FROM MyNumbers WHERE num IN (SELECT num FROM MyNumbers GROUP BY num HAVING COUNT(*) = 1);

Query3

SELECT Max(num) AS num FROM MyNumbers WHERE num NOT IN (SELECT num FROM MyNumbers GROUP BY num HAVING COUNT(num)>1);

Approach : Using Sorting

Query4

SELECT (SELECT num FROM MyNumbers GROUP BY num HAVING COUNT(num) = 1 ORDER BY num DESC LIMIT 1) AS num;

Query5

SELECT IF(COUNT(num) =1, num, null) AS num FROM MyNumbers GROUP BY num ORDER BY COUNT(num), num DESC LIMIT 1;

Query6

SELECT COALESCE ((SELECT num FROM MyNumbers GROUP BY num HAVING COUNT(num) = 1 ORDER BY num DESC LIMIT 1), null) AS num;

• We can also use 'IFNULL' in place of 'COALESCE'

Query7

SELECT num FROM MyNumbers GROUP BY num HAVING count(num) = 1 UNION ALL SELECT NULL ORDER BY num DESC LIMIT 1;

---------------------------------------------------1%

# Write your MySQL query statement below
SELECT MAX(s1.num) AS num
FROM (SELECT num FROM MyNumbers
GROUP BY 1
HAVING Count(num)=1) AS s1;