Generate a sequence number based upon a ORDER BY column

[penn rabb]

I have a query that works and I am trying to build a rank column. Consider the query:

SELECT item,weight,group_id FROM table ORDER BY group_id ASC, weight DESC

I have the following data displayed:

item,weight,group_id
Table,150,1
Chair,75,1
Lamp,3,1
Bookcase,200,2
Cabinet,150,2
Ladder,15,2
Server,40,3
Monitor,15,3
Keyboard,2,3

What I want to do is build a group_rank column with a sequence number according to group_id and the descending weight such that:

item,weight,group_id,group_rank
Table,150,1,1
Chair,75,1,2
Lamp,3,1,3
Bookcase,200,2,1
Cabinet,150,2,2
Ladder,15,2,3
Server,40,3,1
Monitor,15,3,2
Keyboard,2,3,3

o sql gurus hear my plea...