SQL | LeetCode 619. Biggest Single Number (SQL GROUP BY...HAVING)

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.

 

• GROUP BY num 해서 각 숫자가 몇 번 나왔는지 세기
• HAVING COUNT(*) = 1 조건으로 딱 한 번만 등장한 숫자만 필터링
• MAX(num) 사용해서 가장 큰 숫자 하나만 추출

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

 

 

• GROUP BY num → 숫자별로 묶기
• HAVING COUNT(*) = 1 → 등장 횟수가 1번인 것만 남기기
• MAX(num) → 그 중에서 가장 큰 값 고르기

SELECT MAX(num) AS num -- subquery에서 맥시멈을 리턴
FROM (
    SELECT num FROM MyNumbers GROUP BY num HAVING COUNT(*) = 1 -- num끼리 그룹핑 후 싱글 넘버 카운트 함
) AS nums