SQL function last_value

19 October 2022

Sometimes it is good to read the Server documentation carefully when you need in depth information about a function. For a query I needed the last value within a partition. As an example for each customer I needed all sales dates and also the most recent sales date, something like this:

CustomerSales DateLast Sales Date
John Doe12/12/20215/5/2022
John Doe5/5/20225/5/2022
Forrest Gump1/1/20208/8/2022
Forrest Gump4/4/20218/8/2022
Forrest Gump8/8/20228/8/2022
So, I used the function last_value with partion by customer and order by the sales date . And after running the query the results were incorrect for the last sales date. For each row I got only the latest sales date untill that row. At that moment I could not figure out what I was missing and suddenly I remember that you can use an extra clause in the over part.

To be honest, I didn't know by heart what it was and how to use it. So I read the online documentation about the over clause and I forgot to use the correct windows frame. The default window frame is RANGE UNBOUNDED PRECEDING AND CURRENT ROW (as mentioned in the documentation) and now I understood my query result and it was "correct" with respect to its default windows frame. I changed the query to use the correct window frame with ROWS BETWEEN UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING and right now I was happy with the results!

Of course I could solve this with e.g. a nested sub query but I want to solve this with the available function. And I believe this is a more elegant solution. Maybe the other solution is better from a performance point of view, I didn't test this.

Happy SQL!