Invalid column name when using a COALESCE field with Row_Number()

Question

I have this query which works fine with SQL Server 2008:

 SELECT COALESCE(LastName + ', ' + FirstName, LastName, FirstName) [CalculatedEmployeeName] 
 FROM Emp_General 
 ORDER BY [CalculatedEmployeeName] ASC

(Notice that it is ordering by the CalculatedEmployeeName field and not complaining)

When I add a Row_Number field as follows:

 SELECT COALESCE(LastName + ', ' + FirstName, LastName, FirstName) [CalculatedEmployeeName]
 , Row_Number() Over (Order BY [CalculatedEmployeeName] ASC) RecordNumber
 FROM Emp_General
 ORDER BY [CalculatedEmployeeName] ASC

I get an error of:

Invalid column name 'CalculatedEmployeeName'.

Any ideas on why it is complaining?

Note that I have already tried using

row_number() OVER (ORDER BY (SELECT 0))

and

row_number() over (order by @@rowcount)

as discussed in:

SQL Row_Number() function in Where Clause without ORDER BY?

But they do not return the row_number in the correct order.

score 1 · Accepted Answer · answered Apr 07 '14 at 17:19

1

try like this

 ;with cte as
    (
    SELECT COALESCE(LastName + ', ' + FirstName, LastName, FirstName) [CalculatedEmployeeName]
     FROM Emp_General
    )

    select *,Row_Number() Over (Order BY [CalculatedEmployeeName] ASC)  as RecordNumber from cte ORDER BY [CalculatedEmployeeName] ASC

answered Apr 07 '14 at 17:19

vhadalgi

7,027
6
38
67

1

This accomplished what I needed. This way, I can have several other fields in the original select statement and create a Row_number based on any number of them being sorted in any way. Then in the final select, all I have to do is sort on the calculated row_number field. ALSO (and why i needed it.) It allows me to do paging based on arbitrary sorts. – Scott Savage Apr 07 '14 at 17:47

score 0 · Answer 2 · answered Apr 07 '14 at 17:16

Change to this:

SELECT COALESCE(LastName + ', ' + FirstName, LastName, FirstName) [CalculatedEmployeeName]
     , Row_Number() Over (Order BY COALESCE(LastName + ', ' + FirstName, LastName, FirstName) ASC) RecordNumber
FROM Emp_General
ORDER BY [CalculatedEmployeeName] ASC

Cannot reference derived columns in the SELECT

score 0 · Answer 3 · answered Apr 07 '14 at 17:19

You cannot use the name you specified in this query, you have to use the full name:

SELECT COALESCE(LastName + ', ' + FirstName, LastName, FirstName)  [CalculatedEmployeeName], 
Row_Number() Over (ORDER BY COALESCE(LastName + ', ' + FirstName, LastName, FirstName) ASC) RecordNumber
FROM Emp_General

Or, alternatively, you can use something like:

;with Data as (
    SELECT COALESCE(LastName + ', ' + FirstName, LastName, FirstName) [CalculatedEmployeeName] 
    FROM Emp_General 

) SELECT CalculatedEmployeeName, Row_Number() 
      OVER(Order By CalculatedEmployeeName Asc) RecordNumber FROM Data 
ORDER BY [CalculatedEmployeeName] ASC

using the full name gets an Incorrect syntax near 'Row_Number'. — Scott Savage, Apr 07 '14 at 17:26

score 0 · Answer 4 · answered Apr 07 '14 at 17:19

You can use subquery to access derived column

SELECT Z.[CalculatedEmployeeName],
       Row_Number() Over (Order BY Z.[CalculatedEmployeeName] ASC) RecordNumber
FROM 
(
 SELECT COALESCE(LastName + ', ' + FirstName, LastName, FirstName) [CalculatedEmployeeName] 
 FROM Emp_General 
) Z
 ORDER BY Z.[CalculatedEmployeeName] ASC

score 0 · Answer 5 · answered Jan 13 '16 at 00:30

The reason for this behavior is that ORDER BY is the last in the order of the computation of the SQL server. SQL order of operations is: FROM clause
WHERE clause
GROUP BY clause
HAVING clause
SELECT clause
ORDER BY clause

(data from here: http://www.bennadel.com/blog/70-sql-query-order-of-operations.htm)

So SQL can order by this computed column, but it cannot do ROW_NUMBER on it. As suggested above, you can either use cte or write explicitly the calculation.

Invalid column name when using a COALESCE field with Row_Number()

5 Answers5