619.Biggest Single Number

* Problem

A single number is a number that appeared only once in the MyNumberstable. Find the largest single number. If there is no single number, report null.

 

* Explanation

MyNumbers table:
+-----+
| num |
+-----+
| 8   |
| 8   |
| 3   |
| 3   |
| 1   |
| 4   |
| 5   |
| 6   |
+-----+
Output: 
+-----+
| num |
+-----+
| 6   |
+-----+

The single numbers are 1, 4, 5, and 6.
Since 6 is the largest single number, we return it.

Input: 
MyNumbers table:
+-----+
| num |
+-----+
| 8   |
| 8   |
| 7   |
| 7   |
| 3   |
| 3   |
| 3   |
+-----+
Output: 
+------+
| num  |
+------+
| null |
+------+

There are no single numbers in the input table so we return null.

* Solution (Success)

# Write your MySQL query statement below
with UniqueNum as (
    select num
        from MyNumbers
        group by num
        having count(num) = 1
)
select max(num) as num
    from UniqueNum

 

* 되짚어보기

with절을 사용하다 보니 과거의 다는 어떻게 쿼리를 구성하였었는가? 하는 의문을 갖게 되었다.

다음에는 문제 풀이가 아닌 with절과 sub query의 차이를 비교해 봐야겠다.