The following query errors with an invalid column name 'rowid'

  SELECT row_number() over (partition by sales_rep order by timestamp desc) as rowid, *
  FROM dbo.you_gettheidea
where rowid = 1

However, the following version works perfectly. I'm not sure why.

with t1 as (SELECT row_number() over (partition by sales_rep order by timestamp desc) as rowid, *
FROM dbo.you_gettheidea)

Select * from t1
Where rowid = 1

SQL Server 12.0.2000

Edit: It appears that this question is in line with the following answered question

CodePudding user response：

You can't use column alias in where clause of same statement. It doesn't obey the execution order that's why CTE is needed. Please check Execution order

1. FROM and JOIN s. The FROM clause, and subsequent JOIN s are first executed to determine the total working set of data that is being queried.
2. WHERE.
3. GROUP BY.
4. HAVING.
5. SELECT.
6. DISTINCT.
7. ORDER BY.
8. LIMIT / OFFSET / TOP

CodePudding user response：

Late answer, but there is another option without a CTE or subquery ... WITH TIES

Select top 1 with ties *
 From  dbo.you_gettheidea
 Order By row_number() over (partition by sales_rep order by timestamp desc)