inserting\update line numbering

I have a table which contains order information, which I would like to have line number associated with them
what SQL statement do I use in order to add the line numbering for each line, and have it dependent on reseting on the sales order number?If this is a one-time operation, usually a temp table is created with an identity. In other cases you may need to add an identity column to the table.|||If this is a one-time operation, usually a temp table is created with an identity. In other cases you may need to add an identity column to the table.

This is a daily build operation

Using Identity though creates the numbering for all records in the table (sees it as one order (500 records, records numbered from 1 to 500?)

I am looking at the line numbering to reset back to 1 everytime there is a change in the order number field (inv_ref field)

this can't be done through identity?|||My guess is, that it would be possible using an identity column, but I wouldn't go for that if it needs to be reset on a daily basis.

It's probably me, but I'm still not quite clear on what you want, on the other hand, maybe I do but miss the point as to why you need a linenumber associated with the table contents.

One of these might work for you though:
- create a view that has a computed column (if the linenumber can be determined on other information from the table);
- create a trigger that does an update (guess this can be quite a burden);
- create an sp; do an update based on identity from a temp-table.|||line number id forms part of the primary key make up.

I have Invoice number, sales order number, and line id

I can't include product id instead of line id as in an invoice there may be a reference to the same product id i.e. at line 1 and 10.

I guess a messy way of going about it is to just use identity and leave the count go on the entire table just to satisfy the primary key requirements.