CODEWITHSUNDEEP
sqlsql-server
Magretto
Magretto

Reputation: 11

SQL Syntax Error with ROW_NUMBER

I am running this query in SQL:

select
--CLAIM_ID,
--sum (paid_amount),
(ROW_NUMBER() OVER (partition by claim_id order BY claim_id) as asdf)
from [FRAUD].[dbo].[MU_GAPA_ADS_CLAIM_ANALYSIS_ALLACCOUNTS]
where (ALTGRP like '48000%') and (svcDAT between '10/01/2016' and '10/01/2016') 
Group By claim_id

However, I heep getting the following error:

Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'as'.

Can someone please help me understand what is going on and how I can solve it?

Thank you.

Upvotes: 1

Views: 3061

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

The problem isn't row_number(). The problem is the parentheses around the column alias:

ROW_NUMBER() OVER (partition by claim_id order BY claim_id) as asdf

You should also fix the date constants:

select CLAIM_ID, sum(paid_amount),
       row_number() over (partition by claim_id order by claim_id) as asdf)
from [FRAUD].[dbo].[MU_GAPA_ADS_CLAIM_ANALYSIS_ALLACCOUNTS]
where ALTGRP like '48000%' and
     svcDAT between '2016-10-01' and '2016-10-01'
Group By claim_id;

All that said, the row_number() is always going to return "1" for this query, so you might as well leave it out.

Upvotes: 2

Related Questions