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.
CodePudding user response：
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;