为什么我在sql server ROW_NUMBER（）中出错？

I'm new to SQL Server and write this query:

我是SQL Server新手并编写此查询：

SELECT  ROW_NUMBER() over (ORDER BY TelNo ) as RowNum,
        Telno  
FROM [ClubEatc].[dbo].[GetOnlineBills]
where RowNum=1

When I run that query, I get this error:

当我运行该查询时，我收到此错误：

Msg 207, Level 16, State 1, Line 11

Msg 207，Level 16，State 1，Line 11

Invalid column name 'RowNum'.

列名称'RowNum'无效。

How can i solve that?

我怎么解决这个问题？

thanks all.

谢谢大家。

3 个解决方案

#1

You can't filter a ranking function result in the same select statement. Use the below script.

您无法在同一个select语句中过滤排名函数结果。使用以下脚本。

with cte_1
as
(
SELECT ROW_NUMBER() over (ORDER BY TelNo ) as RowNum,Telno  FROM [ClubEatc].[dbo].[GetOnlineBills])
SELECT *
FROM cte_1
where RowNum=1

#2

Try it this way,

试试这种方式，

SELECT *
FROM ( SELECT ROW_NUMBER() over (ORDER BY TelNo ) as RowNum, Telno  
        FROM [ClubEatc].[dbo].[GetOnlineBills]
     ) AS tbl
WHERE RowNum=1

#3

You get this error because you cannot use computed column allias in WHERE statement, or even put where ROW_NUMBER() over (ORDER BY TelNo ) = 1 because it is forbidden.

您收到此错误是因为您无法在WHERE语句中使用计算列allias，或者甚至将ROW_NUMBER（）置于其中（ORDER BY TelNo）= 1，因为它被禁止。

You dint need to use CTE or sub-query's, just use TOP 1 WITH TIES with ordering by ROW_NUMBER():

您需要使用CTE或子查询，只需使用TOP 1 WITH TIES并按ROW_NUMBER（）排序：

SELECT TOP 1 WITH TIES Telno  
FROM [ClubEatc].[dbo].[GetOnlineBills]
ORDER BY ROW_NUMBER() over (ORDER BY TelNo)

#1