i need to update a table with ascending recnos within a group (with sorted names)

does anyone know how to solve it in ansi sql ?

id grp name rec
-- --- ----- ---
1 a joe (1)
2 a john (2)
3 b james (1)
4 b jill (2)
5 b jim (3)
6 c jack (1)
7 d jesse (1)

update tab
set rec = ...

Ansi SQL ? Which database ? I cant recollect any way to do it but if you can tell us the database you are using, may be using the database feature the query might be possible.

after some hours of trying i found this sql query with select


select a.grp, a.name, count(*) "rec"
from tab a, tab b
where b.grp = a.grp
and b.name <= a.name
group by a.grp, a.name;


with this result:


id grp name rec
-- --- ----- ---
1 a joe 1
2 a john 2
3 b james 1
4 b jill 2
5 b jim 3
6 c jack 1
7 d jesse 1


but how does an update work ?


PS: i'm using sas, a statistical software, in which i can use 'naked' sql