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.
1 2 3 4 5 6 7 8 9 |
|
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.
1 2 3 4 5 6 7 8 9 10 |
|