619. Biggest Single Number
Description
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.
Solutions
Solution 1: Grouping and Subquery
We can first group the MyNumbers
table by num
and count the number of occurrences of each number. Then, we can use a subquery to find the maximum number among the numbers that appear only once.
SQL Code


Solution 2: Grouping and CASE
Expression
Similar to Solution 1, we can first group the MyNumbers
table by num
and count the number of occurrences of each number. Then, we can use a CASE
expression to find the numbers that appear only once, sort them in descending order by number, and take the first one.
SQL Code

