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

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

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

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

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

Solution

Method 1 - Using Subquery

Code

Sql

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

Alternative:

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