Solved

SQL: Add incrementing Counter to View output

Posted on 2008-10-30
5
391 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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

705 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now