By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test (
Bulletin_Number INT,
Speed INT,
MilePost_From DECIMAL(18,2),
MilePost_To DECIMAL(18,2)
);
INSERT INTO test VALUES
(1502, 5, 430, 429.1),
(1502, 5, 430.5, 430),
(1502, 10, 431, 430.5),
(1502, 10, 432, 431),
(1502, 10, 433, 431),
(1502, 10, 433, 432),
(1512, 10, 20, 21),
(1512, 10, 21, 22),
(1512, 10, 22, 23),
(1512, 5, 23, 24),
(1512, 5, 24, 25);
SELECT Bulletin_Number, Speed,
CASE WHEN x.dir = 1 THEN MAX(MilePost_From) ELSE MIN(MilePost_From) END AS MilePost_From,
CASE WHEN x.dir = 1 THEN MIN(MilePost_To) ELSE MAX(MilePost_To) END AS MilePost_To
FROM (SELECT *, CASE WHEN MilePost_From > MilePost_To THEN 1 ELSE 0 END AS dir FROM test)x
GROUP BY Bulletin_Number, Speed, x.dir
ORDER BY Bulletin_Number, Speed;
| Bulletin_Number | Speed | MilePost_From | MilePost_To |
|---|---|---|---|
| 1502 | 5 | 430.50 | 429.10 |
| 1502 | 10 | 433.00 | 430.50 |
| 1512 | 5 | 23.00 | 25.00 |
| 1512 | 10 | 20.00 | 23.00 |