Getting Distinct Id(FK) by the latest date in SQL Server

I’m currently working on a query where I need to distinct ID based on the latest date created.

Here is my diagram

|  ID  |    ModelID     |    LocationId   |  DateCreated  |
|------+----------------+-----------------+---------------|
|   1  |       1        |        5        |   02/03/2019  |
|   2  |       2        |      null       |   01/14/2019  |
|   3  |       2        |        0        |   02/03/2019  |
|   4  |       2        |        5        |   12/30/2018  |
|   5  |       4        |        3        |   01/10/2019  |
|   6  |       3        |        5        |   02/14/2019  |
|   7  |       2        |        5        |   03/13/2019  |

So my goal is to Distinct ModelId but still show if the LocationId is null or equals to 0 then if LocationId is still the same, Get the Latest DateCreated and show it to the List

Desired output:

    |  ID  |    ModelID     |    LocationId   |  DateCreated  |
    +------+----------------+-----------------+---------------+
    |   1  |       1        |        5        |   02/03/2019  |
    |   2  |       2        |      null       |   01/14/2019  |<-still show
    |   3  |       2        |        0        |   02/03/2019  |<-still show
    |   4  |       2        |        5        |   03/13/2019  |<-The Latest
    |   5  |       4        |        3        |   01/10/2019  |
    |   6  |       3        |        5        |   02/14/2019  |

Here is my current SQL query:

SELECT 
    a.ModelID, a.LocationId,
    (SELECT TOP 1 
         CONVERT(NVARCHAR, DateCreated, 101) 
     FROM 
         db.DeliveryDates 
     WHERE
         isDeleted = 0 
         AND DeliveryId = a.DeliveryId) AS DateCreated,
FROM 
    db.DeliveryCarInfo a
WHERE
    a.isDeleted = 0

Answer

Please Try with below code

DECLARE @DeliveryCarInfo TABLE (id int,modelId int,locationId int null,DateCreated date)

INSERT INTO @DeliveryCarInfo
(
    id,
    modelId,
    locationId,
    DateCreated
)
VALUES
(1  ,1 ,5,'02/03/2019'),
(2  ,2 ,null,'01/14/2019'), 
(3  ,2 ,0,'02/03/2019'),
(4  ,2 ,5,'12/30/2018'),
(5  ,4 ,3,'01/10/2019'),
(6  ,3 ,5,'02/14/2019'),
(7  ,2 ,5,'03/13/2019')

Select X.Id,X.modelId,X.locationId,X.DateCreated from (SELECT *,row_number() OVER (PARTITION by modelId,locationId ORDER BY dateCreated desc)R FROM @DeliveryCarInfo

)X
WHERE X.R=1

ORDER BY X.modelId,X.DateCreated