> Say you have this table:
>
> a integer primary key
> b integer primary key
> c varchar(200)
>
> And rows:
> a b c
> 1 3 What
> 1 5 ever
> 1 9 some
> 2 1 very
> 2 3 useful
> 2 4 data
> 2 7 to
> 3 2 me

you can't have multiple records of a primary key field with the same value.
so the above s not possible unless a+b is a composite PK.

>
> Now I want to renumber column b where a=2, so the result becomes:
>
> a b c
> 1 3 What
> 1 5 ever
> 1 9 some
> 2 1 very
> 2 2 useful
> 2 3 data
> 2 4 to
> 3 2 me
>
> The numbers should start at 1 while keeping the same internal order as
> before, that is, the lowest number before the renumber will be nr 1,
> the second lowest number becomes nr 2 etc.

there is no such thing as "internal order" is natural order and it is no
order at all. so what you think are can do here is not possible. natural
order is just how the records are currently residing on the disk inside the
datapages, from one moment to the next this order may change e.g. after
record deletion(s) and the engine then decides a "new" natural order - it's
not reliable in the way you think it is here.

>
> Is there any smart SQL that would perform this renumbering?
>
> // David
>

not to my knowledge - unless you go back to a desktop database like dbase
and use the recordnumbering or you use a real surrogate PK in your table
above with a generated PK value. Then you can always select the records in
the PK order (asecending) to achieve this task quite simply.
Alan