Description

Table: MyNumbers

Column NameType
numint
  • 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.

Problem Statement

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.

Solution

The problem can be divided into two parts.

  1. Find the frequency of each number num. This can be done using GROUP BY and COUNT() function.
1SELECT num 
2    FROM MyNumbers
3    GROUP BY num

Next, you need to find those numbers which occur only once. This can be done using filter clause.

1SELECT num 
2    FROM MyNumbers
3    GROUP BY num
4    HAVING COUNT(1) = 1
  1. Find the largest number from the numbers which occur only once. This is simply finding the maximum number from the above subquery.
1SELECT MAX(num) AS num
2    FROM num_counts

So, the final solution would be as below.

1WITH num_counts AS (
2    SELECT num 
3        FROM MyNumbers
4        GROUP BY num
5        HAVING COUNT(1) = 1
6) SELECT MAX(num) AS num
7    FROM num_counts