Finding standing off values in a column

Question

I'm trying to build a database that would help me with technical analysis. But currently I can't move past one hurdle - how to find standing off value in a column?

I don't really even know where to start looking.

I have a dummy table with values like this:

ID	Values
1	26
2	29
3	32
4	34
5	36
6	34
7	33
8	34
9	36
10	37

I'd like to find these values as shown here:

Id	Values
1	26
7	33

Answer 1

By standoff values, do you mean local minimums and perhaps local maximums? If that is the case, you could look at the LAG() and LEAD() window functions to compare the current value with the prior and next value.

If you need to use a larger window (to exclude small jitter), You could use MIN() and MAX() with a qualifier something like OVER(ORDER BY Id ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) and then compare those results with the current value.

Because SQL Server limits where window functions can occur (you can't place them in a where clause), you need to wrap these calculations up in a subquery and test the results in your outer query.

A bit of background: Functions like MIN(), MAX(), SUM(), STDEV(), and COUNT() are normally used to combine data in conjunction with a GROUP BY clause in a query. These are generally referred to as aggregate functions. However, these aggregate functions (and some others like LAG() and LEAD) can also be used without GROUP BY by adding an OVER() qualifier that defines their scope. This usage is termed window function. That OVER() qualifier may include a combination of PARTITION BY, ORDER BY, and/or ROWS PRECEDING .. FOLLOWING .. conditions that control the window function behavior.

For example:

SELECT ID, Value
FROM (
    SELECT
        *,
        LAG(Value) OVER(ORDER BY Id) AS PreviousValue,
        LEAD(Value) OVER(ORDER BY Id) AS FollowingValue
    FROM Data
) A
WHERE (A.Value < PreviousValue OR PreviousValue IS NULL)
AND (A.Value < FollowingValue OR FollowingValue IS NULL)

or

SELECT ID, Value
FROM (
    SELECT
        *,
        MIN(Value) OVER(ORDER BY Id ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) AS LocalMinumum
    FROM Data
) A
WHERE A.Value = LocalMinumum

Both of the above produce your desired result. Similar calculations can be done to identifyu local maximums.

See this db<>fiddle for a demo.