CODEWITHSUNDEEP
sqlsql-server
Amit Singh
Amit Singh

Reputation: 8109

Why am I geting null when i use max instead of count

I answered the following question question link. But i fount stringe Behaviour. when i write this

Update product  Set  [order]= Case  when Not Exists (Select * from
 product a where  a.ProductTypeID =product.ProductTypeID  and a.id
 <product.ID )
     tHEN 1
     eLSE 
     ((Select cOUNT([ORDER])+1 from product  b where 
     b.ProductTypeID =product.ProductTypeID  and product.ID <product.id)+1)
    eND

It works well but when i write ...'

Update product  Set  [order]= Case  when Not Exists (Select * from
     product a where  a.ProductTypeID =product.ProductTypeID  and a.id
     <product.ID )
         tHEN 1
         eLSE 
         ((Select Max([ORDER])+1 from product  b where 
         b.ProductTypeID =product.ProductTypeID  and product.ID <product.id)+1)
        eND

It's gives null in else situation i dont understand why?Can Anyone Explain this when i missing why its getting null when i use Max.Here is sql fiddle http://sqlfiddle.com/#!3/1e15d/1 where i use count when i use Max it gives null why?

Upvotes: 0

Views: 160

Answers (2)

chejianchao
chejianchao

Reputation: 21

you can try this(for mysql):

select ifnull(max(column), 0)   when max() return null, it give you 0.

Upvotes: 0

Guffa
Guffa

Reputation: 700472

The difference is that count returns zero for an empty result, but max returns null for an empty result.

You have product.ID <product.id in your condition in the subquery, which will always be false as you are comparing a field to itself. That will make the result from the subquery empty.

It should be b.ID <product.id to compare the value in the table in the subquery to a value in the table in the outer query.

So neither query works as intended, but when you use count you don't get a null value from the empty result.

Upvotes: 1

Related Questions