SQL: Add incrementing Counter to View output

Additonal  request poseted under new question
*** Note this is a SQL 2000 DB

The application we run calls a view, the view output is used in a labeling program. I can modify the view, but cannot modify anything within the application that calls it.

I am looking to add a counter to the views output. Each time the view is called, each row needs to have an incrementing number assigned. The current output of the view is grouped by a field (Order_ID). On each change of this Order ID within the view result, the counter needs to reset to 1


Order_ID    Counter    Carton_ID
100               1             5000
100               2             5001
100               3             5002
101               1             5003
101               2             5004
102               1             5004

As I mentioned, I cannot touch the application, only the view. Is there anyway to accomplish this ??
LVL 1
JDCamAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jamesguCommented:
select order_id, (select count(*) from <table> b where b.order_id = a.order_id and b.carton_id >= a.carton_id) as counter, carton_id
from <table> a

0
JDCamAuthor Commented:
I am not following..
wouldn't that always produce the value of 1 ?
0
jamesguCommented:
no, the sub-query will be evaluated for each row of the outer table (a)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JDCamAuthor Commented:
If I sustitute:
(a) with Order   (header level order table)
(b) with Carton  (detail level order table

I can match the Order_ID between both tables, but not the carton_id. Carton_id exists only in the table carton.
 
0
JDCamAuthor Commented:
let me clarify further.......
forget about the order by order_id, and need to reset on change of order_id. The application calls the view by order_id, thus reteiving cartons for only one order at a time.

It is a many to one relation. For each record in Order there may be multiple records in Carton.  If the view returns 7 rows (7 carton IDs) for a specific order_id, I need to number them 1 thru 7 accordingly.

Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.