How to add sequence number within groups of repeating values

The Problem

This has been asked in various forms a number of times. It occurs when you have groups of numbers in a column e.g.
[tt]
SomeColumn
---------
101
101
102
102
102
102
103
104
104
104
[/tt]
and you want to apply a sequence number within each group of numbers, starting at 1 again for each instance of a new group, so your resulting data will look like this;

[tt]
SomeColumn Sequence
---------- --------
101 1
101 2[red]
102 1 )
102 2 ) An ascending sequence number is created
102 3 ) for each group of numbers e.g. '102'
102 4 )[/red]
103 1
104 1
104 2
104 3
[/tt]

The Solution

Note: After some useful discussions on various threads I've decided to remove my original solution and point you toward the threads containing much better solutions by ESquared, Donutman, NigelRivett and others;

ESquared & Donutman's solution;
thread183-859372

NigelRivetts solution, without requiring temp tables;
thread183-859742

Happy coding,

Nathan