asked on

SQL: Add counter into View results

I have an exisiting view that is called by an application. I am looking to add an incrementing counter to the output. The counter should be reset each time the view is called, and increment for each row of results when run.

Can someone give me push in the right direction how I would do this.

Thanks

Guy Hengel [angelIII / a3]

try to use the ROW_NUMBER() function in the view query.

BrandonGalderisi

An additional "push" would be

select row_number() over (order by Some_field_or_fields_in_your_view) rn, *
from Yourview

JDCam

ASKER

Ok Great,
give me a chance to test it out
Thanks

JDCam

ASKER

here is a snipit what I have...

SELECT Orders.Order_ID, ROW_NUMBER() over(Order by MO.MU_ID) RowNbr, Orders.Ship_To_Customer_Name,......

but get the result..
'ROW_NUMBER' is not a recognized function name.

is this not a standard function ??

JDCam

ASKER

I guess it is important to add the DB is on SQL 2000 (sorry). I dont see this function there. Is there something comparable I can use ??

ASKER CERTIFIED SOLUTION

BrandonGalderisi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JDCam

ASKER

Thanks Brandon,
Sorry about the confusion. I use 2005 Mgr Studio on my desktop, but this particualar DB is still in 2000. I will try as you suggest

JDCam

ASKER

I will ask for additional help with this suggestion under a new question. (and properly categorized)
thanks.