Leetcode 619 - Biggest Single Number Problem

Problem

Table my_numbers contains many numbers in column num including duplicated ones. Can you write a SQL query to find the biggest number, which only appears once.

Examples

Table: my_numbers

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

+---+
|num|
+---+
| 8 |
| 8 |
| 3 |
| 3 |
| 1 |
| 4 |
| 5 |
| 6 |
+---+

For the sample data above, your query should return the following result:

1
2
3
4
5

+---+
|num|
+---+
| 6 |
+---+

Note: If there is no such number, just output null.

Solution

Method 1 - Using Subquery

Code

1
2
3

SELECT max(num) as num FROM my_numbers
WHERE num NOT IN 
	(SELECT num FROM my_numbers GROUP BY num HAVING COUNT(num) > 1) dupes
 
1
2
3
4

SELECT max(num) as num 
FROM
    (SELECT num FROM my_numbers GROUP BY num
    HAVING COUNT(num) = 1) uniqNums