SQLite aggregate refer to calling SELECT statement-CodePudding

I have a table, such as:

TimeValue	Low
14:00	123
14:30	012
15:00	456
15:30	145
16:00	678

I want to return the minimum "Low" that occurs on or after each TimeValue, so expected results would be:

TimeValue	Min(Low)
14:00	012
14:30	012
15:00	145
15:30	145
16:00	678

Have tried:

SELECT
    TimeValue AS thistime,
    (SELECT
        MIN(Low)
    FROM MyTable
    AND TimeValue >= thistime)
FROM MyTable
GROUP BY thistime
;

but obviously SQLite doesn't recognize thistime from the inner SELECT statement. How do we do this?

CodePudding user response：

Instead of the MIN aggregation function, you can use the corresponding homonimous window function, which will return the minimum value for each row and for each partition. In order to group on the hour, you can use the STRFTIME function as follows:

SELECT TimeValue,
       MIN(Low) OVER(PARTITION BY STRFTIME('%H', TimeValue))
FROM tab

Check the demo here.

CodePudding user response：

I think I figured it out:

SELECT
    a.TimeValue,
    (SELECT MIN(Low) FROM MyTable WHERE TimeValue >= a.TimeValue) AS MyResult
FROM MyTable a;