Take last value in sequence

I am trying to insert data into my target table from my source table where in the target table I have an additional column called SaleTo.

SaleTo = the SaleFrom based on the MAX SaleSequence.

Example of the source table:

SaleNo	SaleFrom	SaleSequence
1	Alabama	2
1	Minnesota	1
1	Virginia	3

Example of target table:

SaleNo	SaleFrom	SaleSequence	SaleTo
1	Alabama	2	Virginia
1	Minnesota	1	Virginia
1	Virginia	3	Virginia

Some code I have tried:

SELECT DISTINCT a.SaleNo, 
                MAX(a.SaleSequence ) AS SaleSequence,
                b.SaleFrom
FROM SequenceOrderSource a
INNER JOIN SequenceOrderSource b
ON a.SaleNo= b.SaleNo
--WHERE B.SaleFrom IN (SELECT b.SaleFrom FROM SequenceOrderSource HAVING SaleSequence= MAX(SaleSequence))
GROUP BY a.SaleNo, b.SaleFrom

I would really appreciate any assistance.

Answer

You can use the last_value. The default range is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW so you need to specify the clause explicitly.

SELECT SaleNo, SaleSequence, SaleFrom,
   last_value(SaleFrom) over(partition by SaleNo order by SaleSequence RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) SaleTo
FROM SequenceOrderSource;