This is my data

I want to find multiple highest values of High column.

For example I want to find maximum value of High column index 1-14, 2-15, 3-16, 4-17, 5-18, 6-19.

How to make different column of this values also?

CodePudding user response：

You can use analytical / windowed functions for this.

SELECT
  *,
  MAX(high) OVER (
    ORDER BY time
    ROWS BETWEEN CURRENT ROW AND 13 FOLLOWING
  )
    AS rolling_14_max_high
FROM
  your_data

This will take the max of the current row and its 13 following rows (when sorted by time).

CodePudding user response：

You can use a window function for this. An OFFSET clause will skip the first 13 rows

SELECT *,
  MaxHigh = MAX(High) OVER (ORDER BY Time ROWS BETWEEN 13 PRECEDING AND CURRENT ROW)
FROM YourTable t
OFFSET 13 ROWS FETCH 10000000 ROWS ONLY;

CodePudding user response：

You can use the start index with range where you want to search maximum value:-

DECLARE @startIndex INT = 5 -- Starting Index
DECLARE @recods INT = 15 -- Number of next rows (Range)

SELECT MAX([Time]) FROM (SELECT [Time]
FROM YouTable
ORDER BY [Time]
OFFSET @startIndex ROWS FETCH NEXT @recods ROWS ONLY) T