Find a largest single number in a table

Write a SQL query to find a largest single number in a table where single number is a number that appeared only once in the table.

If there is there is no single number then return null.

Let’s create a sample table named NumberList and insert some records into this table.

Create table NumberList (Number Int);
Insert into NumberList values (5);
Insert into NumberList values (5);
Insert into NumberList values (3);
Insert into NumberList values (3);
Insert into NumberList values (3);
Insert into NumberList values (8);
Insert into NumberList values (8);
Insert into NumberList values (7);
Insert into NumberList values (7);
Insert into NumberList values (6);

Select * from NumberList;

Solution:

Following T-SQL returns a single largest number.

With cte as 
(
Select Number, count(Number) as Uniqcnt from NumberList
Group by Number
Having Count(Number)=1
),
fnl as
(
select case when uniqcnt =1 then Number else null end Number from cte
)
select max(Number) as Number from fnl

In given table single largest number is 6. Because 8 and 7 both appeared multiple times in a table. After that the 6 is the only largest number which appeared single time.

Let’s check the output of above query. You can see, It returns 6 as a single largest number in a table.

Also Read..

SQL Server Interview Questions