Grokking SQL for Tech Interviews
Ask Author
Back to course home

0% completed

Vote For New Content
4. Biggest Single Number
Table of Contents

Contents are not accessible

Contents are not accessible

Contents are not accessible

Contents are not accessible

Contents are not accessible

Problem

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.

Problem Definition

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.

Example

Image

Output

Image

Try It YourSelf

MYSQL
MYSQL

. . . .

Solution

We can solve this through a query that utilizes a subquery to identify numbers that appear more than once in the MyNumbers table by grouping them and applying a count condition. The main query then retrieves the maximum value from the table, excluding those numbers identified by the subquery.

The step-by-step process involves filtering out non-unique numbers and applying the MAX function to the remaining unique numbers, assigning the result the alias 'num'.

SELECT Max(num) AS num FROM MyNumbers WHERE num NOT IN (SELECT num FROM MyNumbers GROUP BY num HAVING Count(*) > 1;

Let's break down the query step by step:

Step 1: Subquery: Identifying Non-Unique Numbers

SELECT num FROM MyNumbers GROUP BY num HAVING Count(*) > 1;

The subquery is grouping the rows of the MyNumbers table by the num column.

The HAVING COUNT(*) > 1 condition filters the groups to include only those where the count of occurrences of each num is greater than 1.

Output After Step 1:

+-----+ | num | +-----+ | 3 | | 8 | +-----+

Step 2: Main Query: Selecting Maximum Value Among Unique Numbers

SELECT Max(num) AS num FROM MyNumbers WHERE num NOT IN (SELECT num FROM MyNumbers GROUP BY num HAVING Count(*) > 1);

The main query uses the MAX(num) function to find the maximum value from the MyNumbers table.

The WHERE clause filters out rows where the num value is in the set of non-unique numbers identified by the subquery.

Final Output:

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

.....

.....

.....

Like the course? Get enrolled and start learning!

Table of Contents

Contents are not accessible

Contents are not accessible

Contents are not accessible

Contents are not accessible

Contents are not accessible