1

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

ZdWhite
  • 501
  • 1
  • 3
  • 15
  • 1
    Look into SQL *order of execution*. The *where* clause has no visibility of the derived column *rowid* – Stu Nov 24 '21 at 17:59
  • 1
    If I am following [this](https://www.eversql.com/sql-order-of-operations-sql-query-order-of-execution/) correctly, SELECT is 6th, while WHERE is 4th. Thus your comment about visibility makes sense. Thank you – ZdWhite Nov 24 '21 at 18:05
  • If you'd followed that link correctly, `WHERE` is 2nd and `SELECT` is 6th, so the `WHERE` cannot see any results from later operations – Charlieface Nov 24 '21 at 21:44

2 Answers2

2

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
Charlieface
  • 52,284
  • 6
  • 19
  • 43
Kazi Mohammad Ali Nur Romel
  • 15,301
  • 2
  • 13
  • 26
2

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)
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
  • I could not get `with ties` to work at all for my purposes. The version of SQL I am running kept trying to interpret it as a CTE. – ZdWhite Nov 24 '21 at 19:00
  • @ZdWhite Without more context, it is hard to visualize your problem. Just for fun, here is a working example https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=14a11693b4027884d413b34365407743 – John Cappelletti Nov 24 '21 at 19:11
  • 1
    hmm well upon retrying this method I did get it to work. The results seem slightly out of order, not sure why but it's seemingly inconsequential. I like this alternative as it is far cleaner than sub-queries or even my preferred CTE. Thank you! – ZdWhite Nov 24 '21 at 19:19
  • 2
    @ZdWhite Full Disclosure: The cte approach ... where RN=1 is a nudge more performant – John Cappelletti Nov 24 '21 at 19:22
  • Oh? Care to explain, my intuition for SQL performance isn't quite there yet. – ZdWhite Nov 24 '21 at 19:24
  • @ZdWhite WITH TIES requires a SORT and this can get expensive with really LARGE tables. – John Cappelletti Nov 24 '21 at 19:26
  • 1
    @ZdWhite I do like this method because there are no extra columns i.e. RN. That said, if my table is millions of rows, I will use the CTE – John Cappelletti Nov 24 '21 at 19:27
  • Thank you for the context, I'm dealing with rows on the order of 10^3 eventually ~10^4th and like 10 columns on an operation/table that updates ~ once a week. So I'm not particularly worried about performance but I'd like to be generally aware. – ZdWhite Nov 24 '21 at 19:33