Cross join with aggregate greatest value

Question

I have the following table, let's call it Segments:

-------------------------------------
| SegmentStart | SegmentEnd | Value |
-------------------------------------
| 1            | 4          | 20    |
| 4            | 8          | 60    |
| 8            | 10         | 20    |
| 10           | 1000000    | 0     |
-------------------------------------

I am trying to join this table with itself, to obtain the following result set:

-------------------------------------
| SegmentStart | SegmentEnd | Value |
-------------------------------------
| 1            | 4          | 20    |
| 1            | 8          | 60    |
| 1            | 10         | 60    |
| 1            | 1000000    | 60    |
| 4            | 8          | 60    |
| 4            | 10         | 60    |
| 4            | 1000000    | 60    |
| 8            | 10         | 20    |
| 8            | 1000000    | 20    |
| 10           | 1000000    | 0     |
-------------------------------------

Basically, I would need to join every row, with every other row that comes after it, then get the MAX() of the value between each of the rows joined previously. Example: if I am joining row 1 with row 3, I would need the MAX(Value) from all of these 3 rows.

What I already done is the following query:

SELECT s1.SegmentStart, s2.SegmentEnd, GREATEST(s1.Value, s2.Value) as Value FROM Segments s1 CROSS JOIN Segments s2 ON s1.SegmentStart < s2.SegmentEnd

This query creates a similar table to the one desired, but the value fields get mixed up in the following way (I've marked between !! the row that differs):

-------------------------------------
| SegmentStart | SegmentEnd | Value |
-------------------------------------
| 1            | 4          | 20    |
| 1            | 8          | 60    |
| 1            | 10         | !20!  |
| 1            | 1000000    | !20!  |
| 4            | 8          | 60    |
| 4            | 10         | 60    |
| 4            | 1000000    | 60    |
| 8            | 10         | 20    |
| 8            | 1000000    | 20    |
| 10           | 1000000    | 0     |
-------------------------------------

The problem is with the GREATEST() function, because it only compares the two rows that are being joined (start-end 1-4, 8-10), and not the whole interval (in this case, it would be 3 rows, the ones with start-end 1-4, 4-8, 8-10)

How should I modify this query, or what query should I use, to get my desired result?

Additional info, that may help: the rows in the original table, are always ordered based on SegmentStart, and there can be no duplicate or missing values. Every interval between x and y will appear only once in the table, with no overlaps, and no gaps at all.

I am using Maria DB 10.3.13.

Answer 1

Something like this?

SELECT
      s1.SegmentStart
    , s2.SegmentEnd
    , MAX(s.Value) as Value 
FROM
    Segments s1
    INNER JOIN Segments s2 ON (
        s2.SegmentEnd > s1.SegmentStart
    )
    INNER JOIN Segments s ON (
            s.SegmentStart >= s1.SegmentStart
        AND s.SegmentEnd <= s2.SegmentEnd
    )
GROUP BY
      s1.SegmentStart
    , s2.SegmentEnd