If have a temporary table declared as following:
CREATE TABLE #tempKleerkast(
id INT IDENTITY NOT NULL PRIMARY KEY,
IDKleerkastPersoon int,
ID_Persoon int,
rk int
)
Now i wish to fill up the table with the values from an actual table where i want to get the latest (Not Max) record for each person. Therefor i'm using the following query:
SELECT
DISTINCT IDKleerkastPersoon AS Expr1, kk.ID_Persoon,
ROW_NUMBER() OVER (PARTITION BY kk.ID_Persoon
ORDER BY kk.IDKleerkastPersoon DESC) AS rk
FROM KleerkastPerPersoon kk
WHERE kk.ID_Persoon IS NOT NULL AND rk = 1
The problem is where is when i wish to use insert into as following:
INSERT INTO #tempKleerkast(IDKleerkastPersoon,ID_Persoon,rk)
SELECT
DISTINCT IDKleerkastPersoon AS Expr1, kk.ID_Persoon,
ROW_NUMBER() OVER (PARTITION BY kk.ID_Persoon
ORDER BY kk.IDKleerkastPersoon DESC) AS rk
FROM KleerkastPerPersoon kk
WHERE kk.ID_Persoon IS NOT NULL AND rk = 1
I get the following error: Invalid column name 'rk'. Any idea why he doesn't recognise the column rk?
Note: The column rk is needed further on in the stored procedure. So i can't exclude it.
No, the problem comes before you write the INSERT
. Try running just the plain SELECT
and you'll see exactly the same error. Logically, WHERE
runs before SELECT
so you can't refer to aliases introduced by the latter in the former.
This should work:
INSERT INTO #tempKleerkast(IDKleerkastPersoon,ID_Persoon,rk)
SELECT IDKleerkastPersoon, ID_Persoon, rk
FROM (
SELECT
IDKleerkastPersoon, kk.ID_Persoon,
ROW_NUMBER() OVER (PARTITION BY kk.ID_Persoon
ORDER BY kk.IDKleerkastPersoon DESC) AS rk
FROM KleerkastPerPersoon kk
WHERE kk.ID_Persoon IS NOT NULL) t
WHERE rk = 1
(I also removed the DISTINCT
since one of the columns listed was also a column listed in the PARTITION BY
clause and we're only selecting one row from each partition)