MySQL 8.0

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

CREATE TABLE data_new (
    created_date TIMESTAMP,
    rake_device VARCHAR(10),
    rounded_geo_lat DECIMAL(8, 6),
    rounded_geo_lng DECIMAL(9, 6),
    idle_or_moving VARCHAR(200)
);
INSERT INTO data_new (created_date, rake_device, rounded_geo_lat, rounded_geo_lng, idle_or_moving)
VALUES
    ('2024-08-01 11:27:00', 'a-b', 28.29, 76.39, 'idle'),
    ('2024-08-01 10:27:00', 'a-b', 28.29, 76.39, 'idle'),
    ('2024-08-01 09:27:00', 'a-b', 28.29, 76.39, 'idle'),
    ('2024-08-01 08:27:00', 'a-b', 28.29, 76.39, 'idle'),
    ('2024-08-01 07:27:00', 'a-b', 28.29, 76.39, 'idle'),
    ('2024-08-01 06:27:00', 'a-b', 28.29, 76.39, 'moving'),
    ('2024-08-01 05:27:00', 'a-b', 28.28, 76.39, 'moving'),
    ('2024-08-01 05:21:00', 'a-b', 28.29, 76.39, 'idle'),
    ('2024-08-01 05:12:00', 'a-b', 28.29, 76.39, 'idle'),
    ('2024-08-01 04:12:00', 'a-b', 28.29, 76.39, 'idle'),
    ('2024-08-01 03:12:00', 'a-b', 28.29, 76.39, 'idle'),
    ('2024-08-01 02:12:00', 'a-b', 28.24, 76.39, 'moving'),
    ('2024-08-01 03:13:00', 'a-b', 28.24, 76.39, 'moving'),
    ('2024-08-01 03:09:00', 'a-b', 28.245, 76.33129, 'moving'),
    ('2024-08-01 05:12:00', 'a-b', 28.324, 76.3921, 'idle'),
    ('2024-08-01 05:12:00', 'a-b', 28.324, 76.3921, 'idle');

Records: 16  Duplicates: 0  Warnings: 0


SELECT * FROM data_new;


created_date
rake_device
rounded_geo_lat
rounded_geo_lng
idle_or_moving


2024-08-01 11:27:00
a-b
28.290000
76.390000
idle

2024-08-01 10:27:00
a-b
28.290000
76.390000
idle

2024-08-01 09:27:00
a-b
28.290000
76.390000
idle

2024-08-01 08:27:00
a-b
28.290000
76.390000
idle

2024-08-01 07:27:00
a-b
28.290000
76.390000
idle

2024-08-01 06:27:00
a-b
28.290000
76.390000
moving

2024-08-01 05:27:00
a-b
28.280000
76.390000
moving

2024-08-01 05:21:00
a-b
28.290000
76.390000
idle

2024-08-01 05:12:00
a-b
28.290000
76.390000
idle

2024-08-01 04:12:00
a-b
28.290000
76.390000
idle

2024-08-01 03:12:00
a-b
28.290000
76.390000
idle

2024-08-01 02:12:00
a-b
28.240000
76.390000
moving

2024-08-01 03:13:00
a-b
28.240000
76.390000
moving

2024-08-01 03:09:00
a-b
28.245000
76.331290
moving

2024-08-01 05:12:00
a-b
28.324000
76.392100
idle

2024-08-01 05:12:00
a-b
28.324000
76.392100
idle


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;

Table 'fiddle.nearest_location' doesn't exist

created_date	rake_device	rounded_geo_lat	rounded_geo_lng	idle_or_moving
2024-08-01 11:27:00	a-b	28.290000	76.390000	idle
2024-08-01 10:27:00	a-b	28.290000	76.390000	idle
2024-08-01 09:27:00	a-b	28.290000	76.390000	idle
2024-08-01 08:27:00	a-b	28.290000	76.390000	idle
2024-08-01 07:27:00	a-b	28.290000	76.390000	idle
2024-08-01 06:27:00	a-b	28.290000	76.390000	moving
2024-08-01 05:27:00	a-b	28.280000	76.390000	moving
2024-08-01 05:21:00	a-b	28.290000	76.390000	idle
2024-08-01 05:12:00	a-b	28.290000	76.390000	idle
2024-08-01 04:12:00	a-b	28.290000	76.390000	idle
2024-08-01 03:12:00	a-b	28.290000	76.390000	idle
2024-08-01 02:12:00	a-b	28.240000	76.390000	moving
2024-08-01 03:13:00	a-b	28.240000	76.390000	moving
2024-08-01 03:09:00	a-b	28.245000	76.331290	moving
2024-08-01 05:12:00	a-b	28.324000	76.392100	idle
2024-08-01 05:12:00	a-b	28.324000	76.392100	idle