SQL Server Paging query using a column alias

Question

I am trying to create a query that I can then use for a paging system here is the query as it stands

SELECT   
   SLCustomerAccountID, 
   CustomerAccountNumber, 
   CustomerAccountName, 
   (MainTelephoneAreaCode +  MainTelephoneSubscriberNumber) AS PhoneNumber,
   Row_Number() over (order by CustomerAccountName) as RowIndex 
FROM         
   SLCustomerAccount
WHERE   
   CustomerAccountName LIKE '%green%' 
   AND RowIndex BETWEEN 10 AND 30
ORDER BY 
   CustomerAccountName

This query errors with

Invalid column name 'RowIndex'.

because I am trying to use the alias, but I am not sure of the correct way to set this so I can use the data that is in the column RowIndex

Here is what the query returns without the where clause

   ID1      ID2      NAME    Number  Row number
__________________________________________
| 12374927| 00010014|Some name| ******| 1|
| 51744   | 6631    |Same name| ******| 2|

possible duplicate of [Referring to a Column Alias in a WHERE Clause](http://stackoverflow.com/questions/8370114/referring-to-a-column-alias-in-a-where-clause) — billinkc, Oct 17 '14 at 11:48

score 2 · Accepted Answer · answered Oct 17 '14 at 11:12

2

You cannot use an alias in the WHERE (only in the ORDER BY). But you can access it from a CTE:

WITH CTE AS
(

    SELECT SLCustomerAccountID, 
           CustomerAccountNumber, 
           CustomerAccountName, 
           (MainTelephoneAreaCode +  MainTelephoneSubscriberNumber) AS PhoneNumber,
           Row_Number() over (order by CustomerAccountName) as RowIndex 
    FROM   SLCustomerAccount
    Where CustomerAccountName LIKE '%green%' 
)
SELECT SLCustomerAccountID, 
        CustomerAccountNumber, 
        CustomerAccountName, 
        (MainTelephoneAreaCode +  MainTelephoneSubscriberNumber) AS PhoneNumber
FROM CTE
WHERE RowIndex Between 10 AND 30
ORDER BY CustomerAccountName

answered Oct 17 '14 at 11:12

Tim Schmelter

450,073
74
686
939

Might as well add a `TOP 30` in the CTE to limit the number of rows. And consider the difference between filtering for 'green' in the CTE or the main SELECT. – AjV Jsy Oct 17 '14 at 11:14
You can use `TOP` and `ORDER BY` in a CTE, in this case it may speed things up a lot if the table is massive, like it may be in a telecoms environment, given that the main SELECT only requires the first 30 rows. – AjV Jsy Oct 17 '14 at 11:20
@AjVJsy: but it's already ordered by `CustomerAccountName`, so the optimizer picks only the 20 rows(10-30) from the result. Do you have a link where it's documented that this can be more efficient? I've never applied a `TOP` clause + additonal `ORDER BY` in the definition of a CTE if i filter by row-number. – Tim Schmelter Oct 17 '14 at 11:21
I'd have to look into that, Tim, I don't have any vast tables to test with! Thanks for the opportunity to re-examine what I thought I knew :) – AjV Jsy Oct 17 '14 at 11:24
@TimSchmelter hi thanks for the response, quick note i had to put the line (MainTelephoneAreaCode + MainTelephoneSubscriberNumber) AS PhoneNumber ad just selecting MainTelephoneAreaCode and MainTelephoneSubscriberNumber and keep the concatenated version under that but apart from that works great, Do you know what the scalability and performance of a solution like this will be? – Oisian2 Oct 17 '14 at 11:59
@Oisian2: it all depends on your indexes, but in general a CTE with row_number which does not reference itself (or is recursive) is very efficient. – Tim Schmelter Oct 17 '14 at 12:04

score 0 · Answer 2 · answered Oct 17 '14 at 11:44

Query executes in following order in SQLSERVER-

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH CUBE or WITH ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP

so here in this case WHERE clause will be executed before the SELECT clause. So the alias column name will not be available in WHERE clause.

SQL Server Paging query using a column alias

2 Answers2