How to calculate maximum and minimum values from different tupples in SQL?

Question

I'm working on a program, that would "predict" the next possible tupple in a table.

One of the features would be the finding of arithmetic progressions, there are different ways to do this:

• Using System.Data.DataTable, check the DataTable.Columns[i].AutoIncrement (I'm working in .Net (C#))
• Using indexes
• Finding arithmetic progressions in the actual data

This question is about the last part. I am capable to get all data from a column, download them into a list, and check if there's an arithmetic progression in the downloaded list, but I'm wondering if there's a simple SQL way to do this, e.g.:

SELECT MAX(Col1 - Previous_Tupple.Col1) FROM Table1
SELECT MIN(Col1 - Previous_Tupple.Col1) FROM Table1

If both results are equal (and not zero), then I have an arithmetic progression in Col1 from Table1.
I have already found a way to access previous and next tupples, using the LAG and LEAD SQL functions, as follows:

SELECT 
  t.Col1 - LAG(t.Col1) OVER (ORDER BY t.Col1)
FROM Table1 t

Now I just have two issues to solve:

• I tried, very naïvely, to launch this query:
  SELECT MAX(t.Col1 - LAG(t.Col1) OVER (ORDER BY t.Col1)) FROM Table1 t
  This failed: Msg 4109, Level 15, Windowed functions cannot be used in the context of another windowed function or aggregate.
• In case of the first tupple, LAG returns NULL, so this might impact MAX and MIN calculations

So my question is: how do I calculate the MAX and MIN of my LAG-based SQL query, while dropping the annoying NULL-value?

Answer 1

It will be a lot easier to answer if you provide DDL and some sample data, preferably as a Fiddle. Nevertheless, you should be able to nest your query as:

SELECT MAX(col1-lag_col1) from
(
    SELECT t.Col1, LAG(t.Col1) OVER (ORDER BY t.Col1) as lag_col1 
    FROM Table1 t
) as tmp

untested

For NULL you need to determine how to interpret it. One way is to assign the identity element for addition:

SELECT MAX(col1-COALESCE(lag_col1, 0)) from
(
    SELECT t.Col1, LAG(t.Col1) OVER (ORDER BY t.Col1) as lag_col1 
    FROM Table1 t
) as tmp

Another is to filter at the outer level:

SELECT MAX(col1-lag_col1) from
(
    SELECT t.Col1, LAG(t.Col1) OVER (ORDER BY t.Col1) as lag_col1 
    FROM Table1 t
) as tmp
WHERE lag_col1 IS NOT NULL