Initially in a regular table, there are two columns Key and ParentKey. The Key gets its value as an identity column – automatically. ParentKey – by the expression
WITH
latest
as
(
SELECT
ProductID,
Date,
[Key],
ROW_NUMBER() OVER (
PARTITION BY ProductID
ORDER BY [Date] DESC -- order by latest Date
) rn
FROM
[MyTable]
)
UPDATE
u
SET
u.[ParentKey] = latest.[Key]
FROM
[MyTable] u
INNER JOIN
latest
ON u.ProductID = latest.ProductID
WHERE
latest.rn = 1
Does it possible to combine both of these two steps simultaneously when creating the View (Key and ParentKey becomes virtual)? I have a solution for the first part of these task – creating Key column in a view. Could it be combined with the step which then sets ParentKey?
Current Code
CREATE VIEW v_Test
AS
SELECT
ProductID
,Date,
CAST (ROW_NUMBER() OVER( ORDER BY [ProductID] ) AS int) as [Key]
-- some expression for ParentKey?
FROM [MyTable]
Desired View output (Key and ParentKey must be processed during view creation)
+-------------------------------------------------
|ProductID | Date | Key | ParentKey
+-------------------------------------------------
|111 | 2017-12-31 | 1 | 3
|111 | 2018-12-31 | 2 | 3
|111 | 2019-12-31 | 3 | 3
|222 | 2017-12-31 | 4 | 6
|222 | 2018-12-31 | 5 | 6
|222 | 2019-12-31 | 6 | 6
|333 | 2017-12-31 | 7 | 9
|333 | 2018-12-31 | 8 | 9
|333 | 2019-12-31 | 9 | 9
>Solution :
If I have understood what you are trying to do correctly then you can put the query with the row number in a CTE or derived table and then reference that in a windowed aggregate to get the max.
CREATE VIEW v_Test
AS
WITH T
AS (SELECT ProductID,
Date,
ROW_NUMBER() OVER(ORDER BY [ProductID] ASC, [Date] ASC ) AS [Key]
FROM [MyTable])
SELECT ProductID,
Date,
[Key],
MAX([Key]) OVER (PARTITION BY [ProductID]) AS [ParentKey]
FROM T
These "Keys" will not be at all stable over time though as they can change after inserts for unrelated products.