sql notes: leetcode#619 biggest single number

Problem

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

1
2
3
4
5
6
7
8
9
10
11

+---+
|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

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

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

Analysis

• Get numbers that appear only once:

1

SELECT num FROM number GROUP BY num HAVING COUNT(*) = 1;

• Output the max number:

1

SELECT MAX(num) AS num FROM (SELECT num FROM number GROUP BY num HAVING COUNT(*) = 1) AS t;

Solution

1

SELECT MAX(num) AS num FROM (SELECT num FROM number GROUP BY num HAVING COUNT(*) = 1) AS t;

Link

619. Biggest Single Number
(中文版) SQL 笔记: Leetcode#619 Biggest Single Number