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

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?

• For future reference, please take a look at minimal reproducible example for easy instructions on creating a good answerable question. Aug 20, 2021 at 19:35

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
``````
• For future posts please include a fiddle or similar that demonstrates the problem. You will get more and better answers that way. Most people just skip to the next question if there ain't an easy way to reproduce the problem. Aug 20, 2021 at 9:09
• Ok, I'll to that. Aug 20, 2021 at 9:24
• I'm sorry, I was a bit too fast to accept your answer, it seems not to be correct after all: I am working on a table, containing 200 entries, and this SQL query gives 874 results, causing the `MAX()` calculation to be wrong: `SELECT t.Col1, LAG(t.Col1) OVER (ORDER BY t.Col1) as lag_col1 FROM Table1 t`. Aug 20, 2021 at 13:50
• @Dominique: It would be great if you could provide a data sample and the expected result for it in your question, so that whoever is trying to answer your question would have both something to work with and something to compare the results to. Your verbal description is perfectly relevant, but having an actual example alongside to illustrate the problem is immensely helpful. Makes finding a solution much more efficient and prevents an answerer from wasting too much time. Aug 20, 2021 at 14:12
• @AndriyM: I'm terribly sorry, but the mistake is on my part: I'm just starting working with MS-SQL and the DB handling program (MS SQL Server Management Studio) is using an "Edit 200 ..." as a basic viewer, which explain the limited (200) entries. Aug 20, 2021 at 14:30