Would anyone please give me some help on this? I have a table, it contains several columns, the most important are these:

plant - char(3)
item - char(10)
supplier - char(10)
part - char(10)
rank - number

My question is this: how can I write an update statement that would update the field 'rank' in a sequential manner? Take this example (the columns would match the layout above).

001 item1 s001 1234 6
001 item1 s002 1111 7
001 item1 s004 2222 2
001 item1 s005 4445 5

What I want is to be able to resequence the last field (rank) starting with the value 1 and clearing up any 'gaps' in the process, incrementing by the value of 1. The end result should look like this:

001 item1 s001 1234 3
001 item1 s002 1111 4
001 item1 s004 2222 1
001 item1 s005 4445 2

Can this be done with a SQL update statement? The rows being updated may or may not be 'in sequence' by rank. That is why I want to be able to resequence the ranking, starting at 1 and incrementing by 1.

thanks for any help...