?
Solved

SQL: Add incrementing Counter to View output

Posted on 2008-10-30
5
Medium Priority
?
402 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
 
LVL 1

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 2000 total points
ID: 22843854
no, the sub-query will be evaluated for each row of the outer table (a)
0
 
LVL 1

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
 
LVL 1

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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

592 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