Create A Sequence Number Within Group OF Rows 1

[Skittle]

I have a table of rows that can have a variable number of rows per invoice.

I need each row number to have a sequence number within the invoice.
It does not matter the sequence, I just need to generate a number.

So from:-

Invoice Value
00001 10
00001 20
00001 100
00020 5
00020 11
00031 16
00031 2
00031 120

I need:-

Invoice Value Sequence
00001 10 1
00001 20 2
00001 100 3
00020 5 1
00020 11 2
00031 16 1
00031 2 2
00031 120 3

The rows are taken from another table where a sequence number does exist but I am filtering out certain rows based on characteristics so need
to build the sequence number again for the subset.
Not sure how do this.
Any help would be appreciated.



Dazed and confused.

Remember.. 'Depression is just anger without enthusiasum'.

 

[djj55]

Check out the ROW_NUMBER function setting partition as Invoice

SELECT Invoice, Value, ROW_NUMBER() OVER (PARTITION BY Invoice ORDER BY Value) AS Sequence

This is not exact but should help

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[Skittle]

That's pointed me in the right direction.
Many thanks.
Opened my eyes to a host of possibilities there.


Dazed and confused.

Remember.. 'Depression is just anger without enthusiasum'.

 

[djj55]

Glad I could help.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!