Count Since Last Max Within Window

Question

I have been working on this query for most of the night, and just cannot get it to work. This is an addendum to this question. The query should find the "Seqnum" of the last Maximum over the last 10 records. I am unable to limit the last Maximum to just the window.

Below is my best effort at getting there although I have tried many other queries to no avail:

SELECT [id], high, running_max, seqnum,  
  MAX(CASE WHEN ([high]) = running_max THEN seqnum END) OVER (ORDER BY [id]) AS [lastmax]     
  FROM (  
  SELECT [id], [high],  
      MAX([high]) OVER (ORDER BY [id] ROWS BETWEEN  9 PRECEDING AND CURRENT ROW) AS running_max,   
      ROW_NUMBER() OVER (ORDER BY [id]) as seqnum   
  FROM PY t 
  ) x

When the above query is run, the below results.

 id |  high  | running_max | seqnum | lastmax |
+----+--------+-------------+--------+---------+
|  1 |  28.12 |       28.12 |      1 |       1 |
|  2 |  27.45 |       28.12 |      2 |       1 |
|  3 |  27.68 |       28.12 |      3 |       1 |
|  4 |   27.4 |       28.12 |      4 |       1 |
|  5 |  28.09 |       28.12 |      5 |       1 |
|  6 |  28.07 |       28.12 |      6 |       1 |
|  7 |   28.2 |        28.2 |      7 |       7 |
|  8 |   28.7 |        28.7 |      8 |       8 |
|  9 |  28.05 |        28.7 |      9 |       8 |
| 10 | 28.195 |        28.7 |     10 |       8 |
| 11 |  27.77 |        28.7 |     11 |       8 |
| 12 |  28.27 |        28.7 |     12 |       8 |
| 13 | 28.185 |        28.7 |     13 |       8 |
| 14 |  28.51 |        28.7 |     14 |       8 |
| 15 |   28.5 |        28.7 |     15 |       8 |
| 16 |  28.23 |        28.7 |     16 |       8 |
| 17 |  27.59 |        28.7 |     17 |       8 |
| 18 |   27.6 |       28.51 |     18 |       8 |
| 19 |  27.31 |       28.51 |     19 |       8 |
| 20 |  27.11 |       28.51 |     20 |       8 |
| 21 |  26.87 |       28.51 |     21 |       8 |
| 22 |  27.12 |       28.51 |     22 |       8 |
| 23 |  27.22 |       28.51 |     23 |       8 |
| 24 |   27.3 |        28.5 |     24 |       8 |
| 25 |  27.66 |       28.23 |     25 |       8 |
| 26 | 27.405 |       27.66 |     26 |       8 |
| 27 |  27.54 |       27.66 |     27 |       8 |
| 28 |  27.65 |       27.66 |     28 |       8 |
+----+--------+-------------+--------+---------+

Unfortunately the lastmax column is taking the last max of all the previous records and not the max of the last 10 records only. The way it should result is below:

It is important to note that their can be duplicates in the "High" column, so this will need to be taken into account.

Any help would be greatly appreciated.

This is very difficult for us to test when you supply sample data as an image. You should not expect the us to have to transcribe your data for you. — Thom A, Nov 24 '19 at 14:50
Sorry about that, still getting used to Stack Overflow. I just edited, please let me know if this is the preferred format. — JDG, Nov 24 '19 at 14:57
Hi Gordon, It absolutely did return those results. At ID# 18 for example, the last time the high = running_max is at ID#8 which is why its showing 8 instead of 14 like it should. Ive never used SQL Fiddle before so will have to look into it. — JDG, Nov 24 '19 at 15:03
@JDG . . . Really good question (would have been "excellent" if I didn't have to create the Fiddle data myself). I have no idea why it was downvoted. — Gordon Linoff, Nov 24 '19 at 15:19

Gordon Linoff · Accepted Answer · 2019-11-24T15:19:33.657

This isn't a bug. The issue is that high and lastmax have to come from the same row. This is a confusing aspect when using window functions.

Your logic in the outer query is looking for a row where the lastmax on that row matches the high on that row. That last occurred on row 8. The subsequent maxima are "local", in the sense that there was a higher value on that particular row.

For instance, on row 25, the value is 26.660. That is the maximum value that you want from row 26 onward. But on row 25 itself, then maximum is 28.230. That is clearly not equal to high on that row. So, it doesn't match in the outer query.

I don't think you can easily do what you want using window functions. There may be some tricky way.

A version using cross apply works. I've used id for the lastmax. I'm not sure if you really need seqnum:

select py.[id], py.high, t.high as running_max, t.id as lastmax  
from py cross apply
     (select top (1) t.*
      from (SELECT top (10) t.*
            from PY t 
            where t.id <= py.id
            order by t.id desc
           ) t
      order by t.high desc
     ) t;

Here is a db<>fiddle.

Thanks Gordon, looks like this works! Really appreciate the help. — JDG, Nov 24 '19 at 15:18
@JDG . . . The explanation for why your version doesn't work is quite tricky. But basically, you want to compare the `lastmax` from one row with the `high` from another row -- but window functions are comparing values only within a single row. — Gordon Linoff, Nov 24 '19 at 15:20

Count Since Last Max Within Window

1 Answers1