How can I get the last items by date desc then by id desc?

Question

I have a employee course table which holds the current record for each of my employees who have some courses that they have taken. Then I have a history that keeps a static version of the save data with some more metadata. The histories can be inserted in any order. That is the importer can just put them in any order. With each history that is added there is a next due date which states when they have to take the course again. I need the live employee course table to be updated with the latest record from the history ordered by NextDueDate Desc, then by Id Desc. There are two properties(HasAttachments, UniqueId) that need to be copied from the history table. The employeeId and companycourseid act as a reference to the history table.

Here is the code I came up with. I think this will work but I need some second opinions.

WITH History AS (
    SELECT  Id, h.EmployeeId, CompanyCourseId, NextDueDate, HasAttachments, 
        ROW_NUMBER() OVER (Partition BY EmployeeId,CompanyCourseId ORDER BY 
        NextDueDate DESC, Id DESC) AS rownum
    FROM TrainingHistory h
),
Latest AS 
(
    SELECT h.* FROM History h
    WHERE h.rownum = 1
)
 select ec.Id, l.HasAttachments
  INTO TempEmployeeCourse from Latest l INNER JOIN 
      EmployeeCourse ec on l.CompanyCourseId = ec.CompanyCourseId AND 
      l.EmployeeId = ec.EmployeeId

I just put the results I need for updating into a temp table so I can easily update the required UniqueId and HasAttachments columns into the employee course table.

update EmployeeCourse
set HasAttachments = ISNULL(
        (select HasAttachments from dbo.TempEmployeeCourse where Id 
         EmployeeCourse.Id), 0),
UniqueId = ISNULL(
        (select UniqueId from dbo.TempEmployeeCourse where Id = 
         EmployeeCourse.Id), newid())
GO

DROP TABLE [dbo].TempEmployeeCourse
GO

This seems like the most elegant solution. Is there anything I might be missing here?

EmployeeCourse

CREATE TABLE [dbo].[EmployeeCourse](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [EmployeeId] [int] NOT NULL,
    [CompanyCourseId] [int] NOT NULL,
    [HasAttachments] [bit] NOT NULL,
    [UniqueId] [uniqueidentifier] NULL,

 CONSTRAINT [PK_Employee_Courses] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

TrainingHistory

CREATE TABLE [dbo].[TrainingHistory](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [EmployeeId] [int] NULL,
    [NextDueDate] [date] NULL,
    [CompanyCourseId] [int] NULL,
    [UniqueId] [uniqueidentifier] NULL,
    [HasAttachments]  bit NOT NULL DEFAULT 0,
 CONSTRAINT [Training_History$PrimaryKey] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Answer 1

Your query seem ok.

you should thoroughly Test the Resultset in all server.

Only concern is that when it is ISNULL then Why you will update with 0.Is it really necessary ?

Also second CTE is not required.

WITH History AS (
    SELECT  Id, h.EmployeeId, CompanyCourseId, NextDueDate, HasAttachments, 
        ROW_NUMBER() OVER (Partition BY EmployeeId,CompanyCourseId ORDER BY 
        NextDueDate DESC, Id DESC) AS rownum
    FROM TrainingHistory h
)

 select ec.Id, l.HasAttachments
  INTO TempEmployeeCourse from History l INNER JOIN 
      EmployeeCourse ec on l.CompanyCourseId = ec.CompanyCourseId AND 
      l.EmployeeId = ec.EmployeeId
where WHERE l.rownum = 1


update EC
set HasAttachments =TEC.HasAttachments 
,UniqueId =TEC.UniqueId  
from EmployeeCourse EC
inner join dbo.TempEmployeeCourse TEC 
on TEC.Id =EC.EmployeeCourse.Id