Sql-server – MAX DATE With NULL VALUE

sql serversql-server-2008t-sql

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?

Best Answer

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

Related Solutions

Sql-server – Returning a result set with multiple rows based on max date

You simply want:

SELECT
    [Customer ID],
    MAX([Some Date]) AS[Latest Date]
FROM[Cust Date TABLE]
GROUP BY
    [Customer ID];

Ok - you've revised it. You now want to order the rows and pick the top one:

WITH numbered AS (
    SELECT
        [Customer ID],
        [Some Date],
        [Balance],
        ROW_NUMBER() OVER (
            PARTITION BY
                [Customer ID]
            ORDER BY
                [Some Date] DESC
        ) AS rownum
    FROM[Cust Date TABLE]
)
SELECT
    [Customer ID],
    [Some Date],
    [Balance]
FROM numbered
WHERE
    rownum = 1;

Sql-server – IF NOT NULL then UPDATE else keep the value of the field

I think this will solve the issue:

UPDATE DeviceAttribute
SET Details = CASE Name
          WHEN 'Accessories'   THEN COALESCE(@Accessories, Details)
          WHEN 'Description'   THEN COALESCE(@Description, Details)
          WHEN 'Specification' THEN COALESCE(@Specification, Details)
          ELSE Details
              END
WHERE DeviceID = 10 ;

or this (to avoid redundant updates):

UPDATE DeviceAttribute
SET Details = CASE Name
          WHEN 'Accessories'   THEN @Accessories
          WHEN 'Description'   THEN @Description
          WHEN 'Specification' THEN @Specification
          ELSE Details
              END
WHERE DeviceID = 10 
  AND ( Name = 'Accessories'   AND @Accessories IS NOT NULL
     OR Name = 'Description'   AND @Description IS NOT NULL
     OR Name = 'Specification' AND @Specification IS NOT NULL
      ) ;

or this, using a table value constructor:

UPDATE da
SET da.Details = upd.Details
FROM DeviceAttribute AS da
  JOIN
    ( VALUES
      ('Accessories'   , @Accessories),
      ('Description'   , @Description),
      ('Specification' , @Specification)
    ) AS upd (Name, Details)
    ON upd.Name = da.Name 
WHERE da.DeviceID = 10
  AND upd.Details IS NOT NULL ;

Best Answer

Related Solutions

Sql-server – Returning a result set with multiple rows based on max date

Sql-server – IF NOT NULL then UPDATE else keep the value of the field

Related Question