0

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?

1

1 Answer 1

2

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
6
  • 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.
    – Dominique
    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.
    – Dominique
    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.
    – Andriy M
    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.
    – Dominique
    Aug 20, 2021 at 14:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.