SQL-Have 2 number columns. Trying to replace a context number with a sequence

I have a data set right now with 3 columns.

Column 1 is Order number and it is sequential in its own right and a foreign key Column 2 is Batch number and it is a sequence all of its own. Column 3 is a time stamp

The problem I have is as follows

Order   Batch   TimeStamp
1         1         
2         2
1         3
3         4
2         5
1         6

I am trying to work out the time differences between batches on a per order basis. Usually I get a sequence number PER orderid but this isnt the case. I am trying to create a view that will do that but my first obstacle is translating those batch sequences into a sequence number PER Order

My ideal Output

Order   Batch   SequenceNumber TimeStamp    
1         1         1
2         2         1
1         3         2
3         4         1
2         5         2
1         6         3

All help is appreciated!!

Solution

This is what row_number() does:

select t.*, row_number() over (partition by order order by batch) as seqnum
from t;

Note: you have to escape the column name order because it is a SQL reserved words. Just don't use reserved words for column names.

row_number() is ANSI standard functionality available in most databases (your question doesn't have a database tag). There are other ways to do this, but row_number() is the simplest.