ROW_NUMBER OVER a new column

Is it possible to order by a new column inside row_number? I would like to do something like this:

s开发者_如何学运维elect text1 + text2 as NEW_TEXT, row_number over (order by NEW_TEXT) from table

but such sorting doesn't work (I get random results).

You have to repeat the expression text1 + text2

select text1 + text2 as NEW_TEXT, 
row_number() over (order by text1 + text2)
from table

Or do it in steps (foo could be a CTE here)

SELECT
    NEW_TEXT,
    row_number() over (order by NEW_TEXT)
FROM
    (
    select text1 + text2 as NEW_TEXT from table
    ) foo

Another option would be to wrap your initial results in a subselect and apply the ROW_NUMBER on this subselect.

SQL Statement

SELECT  NEW_TEXT
        , ROW_NUMBER() OVER (ORDER BY NEW_TEXT)     
FROM    (       
            SELECT  text1 + text2 AS NEW_TEXT       
            FROM    [table]
        ) q         

Test Data

;WITH [table] (text1, text2) AS (
    SELECT '1', '1'
    UNION ALL SELECT '1', '0'
    UNION ALL SELECT '2', '2'
    UNION ALL SELECT '2', '1'
)
SELECT  NEW_TEXT
        , ROW_NUMBER() OVER (ORDER BY NEW_TEXT)     
FROM    (       
            SELECT  text1 + text2 AS NEW_TEXT       
            FROM    [table]
        ) q         

You can do it using sub query like this way :

select NEW_TEXT, row_number() over (order by NEW_TEXT)
from
(
    select Text1 + Text2 as NEW_TEXT
    from TestTable
) TempTable

select text1 + text2 as NEW_TEXT, 
row_number over (order by text1 + text2) 
from table