Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-05-15 : 07:18:37
|
Adam writes "How do I create a procedure that creates and reset a counter value in the result set. For example, I have a result set of ITEMS that has a common field value; Let's say ORDER NUMBER,For each ITEM record I would like an incremental count(ORDERLINECOUNT) based on the ORDER NUMBER.My Result Set should look like this:ORDERNUMBER ITEMID ITEMDESC ORDERLINECOUNT 6510 10 WIGET 1 6510 22 Screws 2 6510 05 Hammer 3 3318 17 Wrench 1 3318 22 Hammer 2 3318 38 Water Pump 3 3318 86 Lawn Mower 4 " |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-05-15 : 07:36:47
|
Does it matter in what order the lines get written? If not you could play around with something like ...select ordernumber, itemid, itemdesc, (select count(*) + 1 from orders o2 where o2.ordernumber = o.ordernumer and o2.itemid < o.itemid) as orderlinecountfrom orders oorder by ordernumer, orderlinecount Jay White{0} |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
kappaccino
Starting Member
2 Posts |
Posted - 2003-05-15 : 12:48:00
|
quote:
Does it matter in what order the lines get written? If not you could play around with something like ...select ordernumber, itemid, itemdesc, (select count(*) + 1 from orders o2 where o2.ordernumber = o.ordernumer and o2.itemid < o.itemid) as orderlinecountfrom orders oorder by ordernumer, orderlinecount Jay White{0}
|
|
|
|
kappaccino
Starting Member
2 Posts |
Posted - 2003-05-15 : 12:57:02
|
[Jay that was simple. I wrote an ugly cursor to handle this problem.Thanks]Does it matter in what order the lines get written? If not you could play around with something like ...select ordernumber, itemid, itemdesc, (select count(*) + 1 from orders o2 where o2.ordernumber = o.ordernumer and o2.itemid < o.itemid) as orderlinecountfrom orders oorder by ordernumer, orderlinecount Jay White{0}[/quote] |
|
|
|
|
|
|
|
|
A counter field in a result set. 
- Jeff