June 13, 2019 at 3:36 pm
declare @DTBEGIN date = '02/01/2018'
declare @DTEND date = '05/22/2019'
SELECT * FROM (
SELECT c.col1,c.col2
,ROW_NUMBER() OVER (
PARTITION BY [c.col2,]
ORDER BY [c.col3] DESC
) AS [ROW NUMBER]
FROM [table1] c
inner join table2 wrk
on c.col2 = wrk.col2
where CAST(CONVERT(VARCHAR,C.col4,101) AS DATETIME) BETWEEN @DTBEGIN AND @DTEND
) AllRecs
WHERE Allrecs.[ROW NUMBER] = 1
ORDER BY Allrecs.[ROW NUMBER] DESC
--I am getting error
Msg 207, Level 16, State 1, Line 6
Invalid column name 'c.col2.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'c.col3'.
The table has these columns.
June 13, 2019 at 5:43 pm
Part of the problem may be there is an errant comma in your Partition:
PARTITION BY [c.col2,]
Should be (if you only want to partition on the one field):
PARTITION BY [c.col2]
Or perhaps something like:
PARTITION BY [c.col2,c.col1]
June 13, 2019 at 6:33 pm
In addition to removing the comma after the partition by column, you can't use an alias in the select...over clause. You'd want to split the row_number out with a CTE or redo the derived table, join. I can't say how to redo it as it looks like something you used an example and redoing what you posted is as simple as removing the join to table2 since it's not used and just get rid of the alias. This part of the documentation explains not using aliases:
SELECT - OVER Clause (Transact-SQL)
Sue
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply