SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select @@version;


(No column name)


Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) 
	Oct  8 2022 05:58:25 
	Copyright (C) 2022 Microsoft Corporation
	Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



CREATE TABLE Device (
    Id	INT,
    Firmware	varchar(512)
);

INSERT INTO Device (Id, Firmware) VALUES
	('1', '1.1'),
	('2', '1.2'),
	('3', '1.1'),
	('4', '1.1'),
	('5', '1.2');

SELECT * FROM Device

CREATE TABLE CommissionedDevice (
    DeviceId	INT,
    PersonId	VARCHAR(512)
);

INSERT INTO CommissionedDevice (DeviceId, PersonId) VALUES
	('1', '2'),
	('2', '4'),
	('3', '1');

Select * from CommissionedDevice

CREATE TABLE GPSLocation (
    DeviceId	VARCHAR(512),
    Longitude	VARCHAR(512),
    Lattitude	VARCHAR(512),
    RecordedDate	Date
);

INSERT INTO GPSLocation (DeviceId, Longitude, Lattitude, RecordedDate) VALUES
	('1', '2', '3', '2025-08-01'),
	('2', '4', '1', '2025-08-01'),
	('3', '1', '3', '2025-08-01'),
	('1', '2', '4', '2025-08-01'),
	('2', '4', '2', '2025-08-01'),
	('3', '1', '6', '2025-08-01'),
	('1', '2', '6', '2025-08-01'),
	('2', '4', '8', '2025-08-01'),
	('3', '1', '9', '2025-08-01');

SELECT * FROM CommissionedDevice


Id
Firmware


1
1.1

2
1.2

3
1.1

4
1.1

5
1.2




DeviceId
PersonId


1
2

2
4

3
1




DeviceId
PersonId


1
2

2
4

3
1


SELECT G.DeviceID, Longitude, 	Lattitude, 	RecordedDate
FROM GPSLocation G
INNER JOIN   CommissionedDevice CD ON CD.DeviceID=G.DeviceID
INNER JOIN  Device D on D.ID=G.DeviceID
ORDER BY G.DeviceID


DeviceID
Longitude
Lattitude
RecordedDate


1
2
3
2025-08-01

1
2
4
2025-08-01

1
2
6
2025-08-01

2
4
8
2025-08-01

2
4
2
2025-08-01

2
4
1
2025-08-01

3
1
3
2025-08-01

3
1
6
2025-08-01

3
1
9
2025-08-01


WITH CTE as
  (
SELECT G.DeviceID, Longitude, 	Lattitude, 	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
INNER JOIN  Device D on D.ID=G.DeviceID
)
SELECT * 
FROM CTE 
WHERE rn=1


DeviceID
Longitude
Lattitude
RecordedDate
rn


1
2
3
2025-08-01
1

2
4
8
2025-08-01
1

3
1
3
2025-08-01
1

DeviceID	Longitude	Lattitude	RecordedDate
1	2	3	2025-08-01
1	2	4	2025-08-01
1	2	6	2025-08-01
2	4	8	2025-08-01
2	4	2	2025-08-01
2	4	1	2025-08-01
3	1	3	2025-08-01
3	1	6	2025-08-01
3	1	9	2025-08-01