update using over order by row_number()

Question

I found some answers to ways to update using over order by, but not anything that solved my issue. In SQL Server 2014, I have a column of DATES (with inconsistent intervals down to the millisecond) and a column of PRICE, and I would like to update the column of OFFSETPRICE with the value of PRICE from 50 rows hence (ordered by DATES). The solutions I found have the over order by in either the query or the subquery, but I think I need it in both. Or maybe I'm making it more complicated than it is.

In this simplified example, if the offset was 3 rows hence then I need to turn this:

DATES, PRICE, OFFSETPRICE  
2018-01-01, 5.01, null  
2018-01-03, 8.52, null  
2018-02-15, 3.17, null  
2018-02-24, 4.67, null  
2018-03-18, 2.54, null  
2018-04-09, 7.37, null

into this:

DATES, PRICE, OFFSETPRICE  
2018-01-01, 5.01, 3.17  
2018-01-03, 8.52, 4.67  
2018-02-15, 3.17, 2.54  
2018-02-24, 4.67, 7.37  
2018-03-18, 2.54, null  
2018-04-09, 7.37, null

This post was helpful, and so far I have this code which works as far as it goes:

select dates, price, row_number() over (order by dates asc) as row_num
from pricetable;

I haven't yet figured out how to point the update value to the future ordered row. Thanks in advance for any assistance.

Please post a sample of your data and the query you have tried so far. — Taher A. Ghaleb, Nov 18 '18 at 01:57

Max Szczurek · Accepted Answer · 2018-11-18T07:55:16.027

LEAD is a useful window function for getting values from subsequent rows. (Also, LAG, which looks at preceding rows,) Here's a direct answer to your question:

;WITH cte AS (
    SELECT dates, LEAD(price, 2) OVER (ORDER BY dates) AS offsetprice
    FROM pricetable  
)
UPDATE pricetable SET offsetprice = cte.offsetprice
FROM pricetable
INNER JOIN cte ON pricetable.dates = cte.dates

Since you asked about ROW_NUMBER, the following does the same thing:

;WITH cte AS (
    SELECT dates, price, ROW_NUMBER() OVER (ORDER BY dates ASC) AS row_num
    FROM pricetable
),
cte2 AS (
    SELECT dates, price, (SELECT price FROM cte AS sq_cte WHERE row_num = cte.row_num + 2) AS offsetprice
    FROM cte
)
UPDATE pricetable SET offsetprice = cte2.offsetprice
FROM pricetable
INNER JOIN cte2 ON pricetable.dates = cte2.dates

So, you could use ROW_NUMBER to sort the rows and then use that result to select a value 2 rows ahead. LEAD just does that very thing directly.

Thanks for the help, Max. The SQL toolbox is often deeper than my reach. — rdone, Nov 18 '18 at 16:19

update using over order by row_number()

1 Answers1