I have a table like this:
| JobID | dateCreated | dateLastUpdated | JobTargetCompleteDate |
| ------------ | ------------ | ------------ | ------------ |
| 123 | 2006-01-26 16:35:00 | 2006-01-26 16:35:00 | NULL |
| 123 | 2006-01-16 14:36:00 | 2006-01-26 12:43:00 | NULL |
| 123 | 2006-01-16 14:28:00 | 2006-03-21 16:20:00 | 2006-02-28 00:00:00 |
| 456 | 2022-12-20 10:46:00 | 2023-02-10 14:14:00 | NULL |
| 456 | 2022-12-16 17:11:00 | 2023-02-10 14:14:00 | 2023-02-10 00:00:00 |
| 789 | 2023-03-29 09:51:00 | 2023-04-06 14:22:00 | 2023-05-31 00:00:00 |
| 789 | 2023-03-27 15:17:00 | 2023-03-29 09:58:00 | NULL |

Desired results:

The logic is:

• if a datelastUpdated is greater than any dateCreated for a jobid, then retrieve that record.
• They can be occasions whereby a jobid can have the same dateLastUpdated - in that case the record with the most recent dateCreated should be retrieved (456 in the example).

I have tried a SQL Group By and a Max on the columns, but this brings throug

SELECT JobId,
       MAX(dateCreated) AS dateCreated,
       MAX(dateLastUpdated) AS dateLastUpdated,
       MAX(targetCompletionDate) AS JobTargetCompleteDate
FROM dbo.tblJobHead
GROUP BY JobId

But that brings in the incorrect value for JobID 456.

I've also tried a RowNumber, but that brings in the incorrect value for jobid 123:

SELECT t1.JobId,
       t1.dateCreated,
       t1.dateLastUpdated,
       t1.JobtargetCompleteDate,
       t1.rn
FROM
(
    SELECT JobId,
           dateCreated,
           dateInstructed,
           dateLastUpdated,
           JobtargetCompleteDate,
             ROW_NUMBER() OVER (PARTITION BY JobId ORDER BY dateCreated DESC,dateLastUpdated desc) rn
    FROM dbo.tblJobHead
) t1
WHERE rn = 1;