row_number function in a view help with OVER SQL not Supported

[katekis]

I need help on a view I am trying to create. In my sql code I am using the row_number() function (see code below). The sql works fine as a query but when I try to create a view I get error:
The OVER SQL construct or statement is not supported.

Please help me re write this sql so I can use it as a view.

SELECT cfk.customerID,
nda.acct_nbr,
nda.App_Description,
row_number() over (partition by LEFT(cfk.cfk_cif_nbr,12) order by nda.nd_acct_nbr) as Acct_Count
FROM cfk
INNER JOIN nda ON cfk.acct_nbr = nda.acct_nbr
AND cfk.appl_code = nda.appl_code

 

[gmmastros]

The problem is.... the view builder wizard thing you are using does not support the OVER statement. What you should do is...

Using SQL Server Management Studio (or Query Analyzer), open a new window.

Copy/paste this....

Create View dbo.[!]AnyViewName[/!]
As
SELECT cfk.customerID,
nda.acct_nbr,
nda.App_Description,
row_number() over (partition by LEFT(cfk.cfk_cif_nbr,12) order by nda.nd_acct_nbr) as Acct_Count
FROM cfk
INNER JOIN nda ON cfk.acct_nbr = nda.acct_nbr
AND cfk.appl_code = nda.appl_code

Change the part in red to give it a meaning name.

Run this code by pressing F5 on your keyboard.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom