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