Question 91661 in Mysql Question posted in
The official MySQL documentation can be found here.

Not sure whats wrong in the below Query of the problem "Biggest Single Number" from leetcoode – Mysql

A single number is a number that appeared only once in the MyNumbers table.

Write an SQL query to report the largest single number. If there is no single number, report null.

The query 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.

My Query :

select  ifnull(num,null) as num 
from MyNumbers 
group by num
having count(num)=1
order by num desc
limit 1;

Why the above query is not returning the null, if there no single numbers

I was expecting to display null for non single number test case,but its showing empty table

Tags:

4

Answers


  1. 0

    count(num) will return 0 for null values. This can be solved by using count(*):

    select  ifnull(num,null) as num 
from MyNumbers 
group by num
having count(*)=1
order by num desc
limit 1
;

    see DBFIDDLE, for a step-by-step solution.

    Login or Signup to reply.
  2. 0

    You can’t return a null value if your result set contains no rows since that still requires a row to be returned and a value to operate on.

    If you want to return a NULL value in the event of no qualifying rows you could create a union of two sets:

    with n as (
  select num 
  from t 
  group by num
  having count(*) = 1
  union all select null
)
select num
from n
order by num desc
limit 1;

    You could also use a case expression to test the number of rows and return a null, this requires that at least some data exists

    select case when Count(*)> 1 then null else num end Num
from t 
group by num
order by Count(*), num desc
limit 1;

    Some fiddle examples

    Login or Signup to reply.
  3. 0

    Write an SQL query to report the largest single number.

    Okay, group by num having count(*) = 1 is just fine for that. This is the tricky part:

    If there is no single number, report null.

    The having will filter out all rows that don’t have a count of one. So if there are no numbers with a count of one, there are 0 rows in the result set. That’s not the same as a result with one row of null.

    An aside: beyond exercise, this is a stupid requirement. It doesn’t make sense to conflate null with an empty result set. An empty result set is the correct way to express the lack of matching data. Okay, this is just an exercise, after all.

    Here is my simple solution. I simply union the reults of the select on MyNumbers with a select of null. Now there’s a null in the result set, and if there’s no other result in the result set, the null is the only one to come out of the limit. If there is a number, that’s returned instead.

    select num from MyNumbers  num group by num having count(num) = 1 
union select null as num
order by num desc limit 1;
    Login or Signup to reply.
  4. 0

    Your query returns no rows when it’s nothing to return. As your original query returns at most a single scalar the simplest way is to make it an expression select (<original scalar query>) as res :

    select (
    select num 
    from MyNumbers 
    group by num
    having count(num)=1
    order by num desc
    limit 1
    ) res
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search