对子查询使用ROW_NUMBER（）OVER（）

I have some T-SQL script that I need to make a ROW_NUMBER OVER() when my FROM table is a subquery.

我有一些T-SQL脚本，当我的FROM表是一个子查询时，我需要做一个ROW_NUMBER OVER（）。

It is forbiden in the SQL Server?

它在SQL Server中被禁止了吗？

See the example below.

请参阅下面的示例。

SELECT 
    ROW_NUMBER() OVER(PARTITION BY PATIENTALERT_ID ORDER BY SOURCE_TIME) AS RN 
        FROM (SELECT 
                MIN(PATIENTALERT_SAMPLE_ID) AS PATIENTALERT_SAMPLE_ID, 
                PATIENTALERT_ID, SOURCE_TIME, TEXT 
            FROM 
                PATIENTALERT_SAMPLE
            GROUP BY 
                PATIENTALERT_ID, SOURCE_TIME, TEXT)

The error result is:

错误结果是：

Msg 102, Level 15, State 1, Line 9 Incorrect syntax near ')'.

消息102，级别15，状态1，行9'''附近的语法不正确。

Thank you for any suport

谢谢你的任何支持

1 个解决方案

#1

The problem is completely unrelated to your ROW_NUMBER. The issue is that you need to add an alias to the derived table:

问题与您的ROW_NUMBER完全无关。问题是您需要为派生表添加别名：

SELECT 
    ROW_NUMBER() OVER(PARTITION BY PATIENTALERT_ID ORDER BY SOURCE_TIME) AS RN 
        FROM (SELECT 
                MIN(PATIENTALERT_SAMPLE_ID) AS PATIENTALERT_SAMPLE_ID, 
                PATIENTALERT_ID, SOURCE_TIME, TEXT 
            FROM 
                PATIENTALERT_SAMPLE
            GROUP BY 
                PATIENTALERT_ID, SOURCE_TIME, TEXT) AS T;