1

Trying to get the max date from my table, i can't retrieve the value when the date is null. Sample :

the following table :

+----+--------+------+------------------------+
| id | parent |    Date                       |
+----+--------+------+------------------------+
|  1 | Alex   |   2018-10-01 00:00:00.0000000 |
|  1 | TIM    |   2012-11-07 00:00:00.0000000 |
|  3 | Cath   |    NULL                       |
+----+--------+------+------------------------+

To get the max date im using the following:

select id, Parent, Date From Table T1
Where Date = (select Max(Date) from Table T2 where T1.id= T2.id )

Wich works fine for the id = 1 But for the id = 3 it's not working

The result is :

1 | Alex   |   2018-10-01 00:00:00.0000000

Any ideas Why?

  • 1
    What is your expected result? IMHO it is correct. – McNets Nov 15 '18 at 14:07
3

Aggregated functions does not consider rows with NULL values.

You could use a WINDOW FUNCTION to enumerate rows and then get first row only:

with ct as
(
    select id, Parent, Date,
           row_number() over (partition by id order by id, date desc) rn
    from   tbl
)
select id, Parent, Date
from   ct
where  rn = 1
order by id;

id | Parent | Date               
-: | :----- | :------------------
 1 | Alex   | 01/10/2018 00:00:00
 3 | Cath   | null              

db<>fiddle here

  • 1
    If the OP wants the max date shouldn't the order by in the window just be date desc? – Aaron Bertrand Nov 15 '18 at 14:20
  • @AaronBertrand you're right, edited. – McNets Nov 15 '18 at 14:21
  • Sweet ! i just changed as @AaronBertrand suggested the order by THANKS a lot! – Moune Nov 15 '18 at 14:34
-2

As McNets states, this is normal SQL behaviour. You have to watch out using NULL's in comparisations since NULL is actually everything.

You might be able to solve this to set a "dummy" value to the NULL value. This way you can select the NULL value as the highest date.

select CASE WHEN MAX(COALESCE(Date, 1000000)) = 1000000 THEN NULL ELSE MAX(Date) END as Date
from Table T2 where T1.id= T2.id
  • Have a look at fiddle I've supplied in my answer, if there is more than one row with null values for the same ID this solution doesn't works. – McNets Nov 15 '18 at 14:24
  • Hi McNets, I have to agree. If the Id has multiple Null-values it will return both rows for this Id. – chittybang Nov 15 '18 at 14:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.