SQL Syntax Error with ROW_NUMBER

Question

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.

And be careful with those date strings. You should use the ANSI standard YYYYMMDD. — Sean Lange
– Sean Lange, Commented Jul 3, 2018 at 13:07
@HoneyBadger what would be the correct syntax, using the appropriate parentheses etc? — Magretto
– Magretto, Commented Jul 3, 2018 at 13:08

Gordon Linoff · Accepted Answer · 2018-07-03 13:07:55Z

2

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.

answered Jul 3, 2018 at 13:07

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Magretto Over a year ago

Is this because I am grouping by Claim_ID only? I will add some other variables to group by which will result in Claim_ID repetitions. Essentially, I would like to get a count of the number of times each claim ID shows up

Cato Over a year ago

sounds like you just need COUNT(claim_id ) AS asdf

Gordon Linoff Over a year ago

@Magretto . . . You should ask another question, with appropriate data and a description of what you want. This seems to answer this question.

Collectives™ on Stack Overflow

SQL Syntax Error with ROW_NUMBER

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related