Prev: Convert Positive Value to Negative Value
Next: SQL2000: Permissions Stored Procedures
From: Mary Phelps on 8 Mar 2010 16:16
I have 2 columns in table Orders. OrderId which is int InventoryId
which is int.
I would like to write a query to update a column called sequence so it
assigns an automatic number based on OrderId and InventoryId. For same
Order Id and different InventoryId the sequence increases. When we get
a new OrderId it restarts from 1.
OrderId InventoryId Sequence
100 505 1
100 508 2
100 907 3
100 908 4
101 400 1
101 1008 2
101 1007 3
101 1000 4

From: RJ Roberts on 8 Mar 2010 17:17
Assuming you use SS2005 or above the following will do nicely. If this is an
ongoing requirement you probably want to modify to suit your requirements.

With SetSequence
AS
(
Select Sequence, Row_Number() Over (Partition by OrderId Order By
InventoryId) as NewSeq
From "YOURTABLE"
)
Update SetSequence
Set Sequence = NewSeq

Good luck
--
RJ Roberts
DB Architect/Developer


"Mary Phelps" wrote:

> I have 2 columns in table Orders. OrderId which is int InventoryId
> which is int.
> I would like to write a query to update a column called sequence so it
> assigns an automatic number based on OrderId and InventoryId. For same
> Order Id and different InventoryId the sequence increases. When we get
> a new OrderId it restarts from 1.
> OrderId InventoryId Sequence
> 100 505 1
> 100 508 2
> 100 907 3
> 100 908 4
> 101 400 1
> 101 1008 2
> 101 1007 3
> 101 1000 4
>
> .
>
From: Eric Isaacs on 8 Mar 2010 21:15
RJ's suggestion of using a row_number is good, but I would suggest
that you find a way to not store the sequence and rather just
calculate it on the fly when it's needed by using the row_number.
Otherwise, you'll need to keep it up to date when rows are deleted or
data is change. The only reason to store a sequence would be if the
sequence is modifiable by the user.

I hope that helps!

-Eric Isaacs
From: --CELKO-- on 9 Mar 2010 12:12
Mind posting some skeleton DDL? That is minimal Netiquette. We need
to know keys, constraints and data types.

SEQUENCE is a reserved word in Standard SQL and it is too vague to be
a data element name. Sequence of what? I hope that you are not just
doing a simple PHYSICAL (ugh!) count instead of a LOGICAL, relational
data element. This is SQL and not 1950's magnetic tape files or
COBOL.
 | 
Pages: 1
Prev: Convert Positive Value to Negative Value
Next: SQL2000: Permissions Stored Procedures