Select 1 row from the dataset which satisfies a particular condition

I require some help.

Database Design:

Device Table

Commissioned Device Table

GPS Location Table

End result that I want is the following:

So the idea is that there can be multiple entries of the Device in the table, but I need to select only the one which have highest RecordedDate. And devices should only be those available in the CommissionedDevice table.

What I have tried?

I have tried various approaches, like distinct, cte, group by, but so far I am unable to achieve the desired results.

Please let me know what approach should be taken, I read there is something like rank which can also help.

Thanks.

You can use Row_Number to number the rows for each device ID. Then use a common table expression (CTE) to show just the rows with rn=1.

WITH CTE as
(
SELECT G.DeviceID, 
       G.Longitude,     
       G.Lattitude,     
       G.RecordedDate, 
       Row_Number() OVER (PARTITION BY G.DeviceID ORDER BY RecordedDate desc) as rn
FROM GPSLocation G
INNER JOIN   CommissionedDevice CD ON CD.DeviceID=G.DeviceID
)
SELECT * 
FROM CTE 
WHERE rn=1

fiddle

fiddle with time added