I've got a problem I'm trying to solve as efficiently as possible. I've attached simplified data, but to help conceptualize it, I want to, for each day in the life of a security, figure out what the highest value was over the past 52 weeks.

In SQL Server 2012+, this is a piece of cake using max() over()... and a row frame, but alas, we don't have 2012+ on any prod boxes.

Two Questions: 1) does anyone know an efficient way to do this (p.s. Quirky updates came to mind, but I couldn't figure out how to apply it to this problem)2) Does anyone know roughly how the max() over() /w row frame works under the covers? Maybe I can reverse engineer it in an efficient manner

Thanks Luis. The only problem there is it doesn't work for a rolling window.

If the highest value is at the tail end of the window and you iterate one row, the high must now change to the second highest value. Then if that remained the high till IT fell out of scope, you'd have to switch to the 3rd highest, and so on.

Can you give me an example on what you just said? Are you trying to do a ranking? Or am I misunderstanding you?Partitioning (using windows) the results is possible, but I'm trying to figure what do you need.

Luis C.General Disclaimer:Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Sure. So turning the data set on its side, you could think of the sequence of values like this:

5,3,2,3,1,4,6....

The update you described works if you just want to know the highest value at any point along the whole series, but I want to know what the highest value was only for the past (lets say) 3 records. So let's start reading from the left to the right (with the current frame indicated by brackets)

1) 5],3,2,3,1,4,6. 5 is the highest (by default)2) 5,3],2,3,1,4,6. 5 is still the highest3) [5,3,2],3,1,4,6. 5 is still the highest4) 5,[3,2,3],1,4,6. 3 is now the highest, but you have no way of knowing this since the highest value, against which we were previously comparing to determine the next in the series, is now out of scope.

If you're aware of a way to use an over clause to partition over a range without the use of ROWS or RANGE, I'd be interested to know.

I totally missed that part and understand your pain.For now, the logic indicates that the "wide" join you're using seems the best option with one slight modification. You can remove the last condition to reduce one third of the logical reads. At least that's been consistent on my tests. I'll try to think further.

Luis C.General Disclaimer:Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Hokay, so I think I have an algorithm to do this with a quirky update. There may be some bugs in it because there are so many combinations of how the elements in the window are arranged, but I think more or less, this does the trick.

Early on I had the idea to store off the second highest value, and then if the first highest value rolled off, the second highest value would become the first. Originally I was storing the second highest value in the window whether it was before or after the first highest value. But after I jotted it down on paper, it became clear that you only need to store the second highest value if its IN FRONT of the first highest. No value smaller than the first highest value, behind it in the sequence, would ever supplant the first highest.

Consider the following sequence and assume a rolling window of 3. F = first highest, S = second highest6,2,5,3,7,1,1,3

All these rules can be done on successive rows, making a quirky update possible. This works for the randomized data I originally asked about, but to show it in SQL, I’ve hard coded this exact sequence into this script.

Note the use of @temp<name> variables. The operations need to take place on the variables as they were when they entered the update. If you were to use the actual variables, as soon as the first clause modified @first, it would throw off the clauses for subsequent variable assignments.

The windowed function in 2012+ I think still works better than mine, but this is considerably better than any of the RBAR solutions in terms of performance.

Wow, that works quite well. Given that I discovered some flaws in my post where I thought I had it with the quirky update, I'll try using this against my actual data set and see if everything holds water.