CODEWITHSUNDEEP
sql-serversql-server-2008
Paulo Teixeira
Paulo Teixeira

Reputation: 458

Use ROW_NUMBER() OVER() with subquery

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

It is forbiden in the 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 ')'.

Thank you for any suport

Upvotes: 1

Views: 2347

Answers (1)

Lamak
Lamak

Reputation: 70648

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

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;

Upvotes: 5

Related Questions