MySQL 8.0

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

CREATE TABLE nearest_location(
created_date TIMESTAMP,
device VARCHAR(255),
rake_device VARCHAR(255),
is_same_location INT,
rounded_geo_lat FLOAT,
rounded_geo_lng FLOAT,
idle_or_moving VARCHAR(255),
time_diff_idle_vs_moving INT,
max_idle INT,
rn_idle INT
);
INSERT INTO nearest_location(created_date, device, rake_device, is_same_location, 
rounded_geo_lat, rounded_geo_lng, idle_or_moving, time_diff_idle_vs_moving, max_idle, 
rn_idle)
VALUES
('2023-12-26 09:58', 'SLA16143', 'ARIL-05-SLA16143', 1, 22.78, 69.67, 'idle', 3, 31, 1),
('2023-12-26 09:55', 'SLA16143', 'ARIL-05-SLA16143', 1, 22.78, 69.67, 'idle', 19, 16, 
2),
('2023-12-26 09:36', 'SLA16143', 'ARIL-05-SLA16143', 1, 22.78, 69.67, 'idle', 60, 10, 
3),
('2023-12-26 08:36', 'SLA16143', 'ARIL-05-SLA16143', 1, 22.78, 69.67, 'idle', 60, 5, 4),
('2023-12-26 07:36', 'SLA16143', 'ARIL-05-SLA16143', 0, 22.78, 69.67, 'moving', 69, 2, 
5),
('2023-12-26 06:27', 'SLA16143', 'ARIL-05-SLA16143', 1, 22.77, 69.67, 'idle', 18, 4, 6),
('2023-12-26 06:09', 'SLA16143', 'ARIL-05-SLA16143', 0, 22.77, 69.67, 'moving', 12, 61, 
7),
('2023-12-26 05:57', 'SLA16143', 'ARIL-05-SLA16143', 1, 22.74, 69.67, 'idle', 17, 3, 8),
('2023-12-26 05:40', 'SLA16143', 'ARIL-05-SLA16143', 0, 22.74, 69.67, 'moving', 11, 60, 
9),
('2023-12-26 05:29', 'SLA16143', 'ARIL-05-SLA16143', 1, 22.74, 69.68, 'idle', 44, 1, 
10);

Records: 10  Duplicates: 0  Warnings: 0


SELECT * FROM nearest_location;


created_date
device
rake_device
is_same_location
rounded_geo_lat
rounded_geo_lng
idle_or_moving
time_diff_idle_vs_moving
max_idle
rn_idle


2023-12-26 09:58:00
SLA16143
ARIL-05-SLA16143
1
22.78
69.67
idle
3
31
1

2023-12-26 09:55:00
SLA16143
ARIL-05-SLA16143
1
22.78
69.67
idle
19
16
2

2023-12-26 09:36:00
SLA16143
ARIL-05-SLA16143
1
22.78
69.67
idle
60
10
3

2023-12-26 08:36:00
SLA16143
ARIL-05-SLA16143
1
22.78
69.67
idle
60
5
4

2023-12-26 07:36:00
SLA16143
ARIL-05-SLA16143
0
22.78
69.67
moving
69
2
5

2023-12-26 06:27:00
SLA16143
ARIL-05-SLA16143
1
22.77
69.67
idle
18
4
6

2023-12-26 06:09:00
SLA16143
ARIL-05-SLA16143
0
22.77
69.67
moving
12
61
7

2023-12-26 05:57:00
SLA16143
ARIL-05-SLA16143
1
22.74
69.67
idle
17
3
8

2023-12-26 05:40:00
SLA16143
ARIL-05-SLA16143
0
22.74
69.67
moving
11
60
9

2023-12-26 05:29:00
SLA16143
ARIL-05-SLA16143
1
22.74
69.68
idle
44
1
10


SELECT
    mid.*,
    ROUND(case when mid.rn_idle = 1 
          then max(case when mid.max_idle = 1 then mid.rounded_geo_lat END) 
               over (partition by mid.device, mid.rake_device) END, 2) as max_idle_lat1,
    ROUND(case when mid.rn_idle = 1 
          then max(case when mid.max_idle = 1 then mid.rounded_geo_lng END) 
              over (partition by mid.device, mid.rake_device) END, 2) as max_idle_lng1
 FROM  nearest_location mid;


created_date
device
rake_device
is_same_location
rounded_geo_lat
rounded_geo_lng
idle_or_moving
time_diff_idle_vs_moving
max_idle
rn_idle
max_idle_lat1
max_idle_lng1


2023-12-26 09:58:00
SLA16143
ARIL-05-SLA16143
1
22.78
69.67
idle
3
31
1
22.74
69.68

2023-12-26 09:55:00
SLA16143
ARIL-05-SLA16143
1
22.78
69.67
idle
19
16
2
null
null

2023-12-26 09:36:00
SLA16143
ARIL-05-SLA16143
1
22.78
69.67
idle
60
10
3
null
null

2023-12-26 08:36:00
SLA16143
ARIL-05-SLA16143
1
22.78
69.67
idle
60
5
4
null
null

2023-12-26 07:36:00
SLA16143
ARIL-05-SLA16143
0
22.78
69.67
moving
69
2
5
null
null

2023-12-26 06:27:00
SLA16143
ARIL-05-SLA16143
1
22.77
69.67
idle
18
4
6
null
null

2023-12-26 06:09:00
SLA16143
ARIL-05-SLA16143
0
22.77
69.67
moving
12
61
7
null
null

2023-12-26 05:57:00
SLA16143
ARIL-05-SLA16143
1
22.74
69.67
idle
17
3
8
null
null

2023-12-26 05:40:00
SLA16143
ARIL-05-SLA16143
0
22.74
69.67
moving
11
60
9
null
null

2023-12-26 05:29:00
SLA16143
ARIL-05-SLA16143
1
22.74
69.68
idle
44
1
10
null
null

created_date	device	rake_device	is_same_location	rounded_geo_lat	rounded_geo_lng	idle_or_moving	time_diff_idle_vs_moving	max_idle	rn_idle
2023-12-26 09:58:00	SLA16143	ARIL-05-SLA16143	1	22.78	69.67	idle	3	31	1
2023-12-26 09:55:00	SLA16143	ARIL-05-SLA16143	1	22.78	69.67	idle	19	16	2
2023-12-26 09:36:00	SLA16143	ARIL-05-SLA16143	1	22.78	69.67	idle	60	10	3
2023-12-26 08:36:00	SLA16143	ARIL-05-SLA16143	1	22.78	69.67	idle	60	5	4
2023-12-26 07:36:00	SLA16143	ARIL-05-SLA16143	0	22.78	69.67	moving	69	2	5
2023-12-26 06:27:00	SLA16143	ARIL-05-SLA16143	1	22.77	69.67	idle	18	4	6
2023-12-26 06:09:00	SLA16143	ARIL-05-SLA16143	0	22.77	69.67	moving	12	61	7
2023-12-26 05:57:00	SLA16143	ARIL-05-SLA16143	1	22.74	69.67	idle	17	3	8
2023-12-26 05:40:00	SLA16143	ARIL-05-SLA16143	0	22.74	69.67	moving	11	60	9
2023-12-26 05:29:00	SLA16143	ARIL-05-SLA16143	1	22.74	69.68	idle	44	1	10