Max Unique Number

  

Write an SQL query to fetch a number that appears only once and is the largest from the table. If there's no unique number, then report Null.

Input :-

Numbers_List table :-
There is no Primary Key column for the Numbers_List table.

Number
2
7
4
2
9
5
6
9

Output :-

Number

7

Query :-

select max(Number) as Number
from Numbers_List
where Number not in
(select Number 
from Numbers_List
group by Number
having count(Number)>1;

Explanation : - 

This SQL query retrieves the maximum value from a column called ‘Number’ in a table called "Numbers_List". However, it excludes any numbers that appear more than once in the table.

Let's break down the query step by step:

• The main part of the query is the 'Select' statement, which retrieves the maximum value from the ‘Number’ column. It uses the 'MAX()' function to accomplish this. The result of this function is aliased as ‘Number’ for the output.
• The 'where' clause is used to filter the rows based on a condition. In this case, the condition is specified as ‘Number not in (subquery)’. The subquery performs a separate query to retrieve all the numbers that appear more than once in the table.
• The subquery uses the 'Group by' clause to group the numbers by their values. The 'having' clause is used to filter the groups based on a condition. In this case, the condition is ‘count(Number) > 1’, which means only groups with more than one occurrence of the number will be selected.

By using the ‘Number not in (subquery)’ condition in the main query's 'where' clause, the query ensures that only the maximum number from the ‘Number’ column is selected, excluding any numbers that appear more than once in the table.