Solved

SQL: Add incrementing Counter to View output

Posted on 2008-10-30
5
395 Views
Last Modified: 2009-02-26
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 ??
0
Comment
Question by:JDCam
  • 3
  • 2
5 Comments
 
LVL 9

Expert Comment

by:jamesgu
ID: 22843670
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
 

Author Comment

by:JDCam
ID: 22843806
I am not following..
wouldn't that always produce the value of 1 ?
0
 
LVL 9

Accepted Solution

by:
jamesgu earned 500 total points
ID: 22843854
no, the sub-query will be evaluated for each row of the outer table (a)
0
 

Author Comment

by:JDCam
ID: 22844140
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
 

Author Comment

by:JDCam
ID: 22844475
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

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question