enumerate groups given a certain value - Mailing list pgsql-sql

Hello,

Here is a sql problem, which I thought simple at first, but for which I
ended up with a solution I find surprisingly complicated.
I really think I could have achieved a much easier way of handling this,
but I do not manage to find the trick allowing a very simple and
efficient query to solve the problem.

Let's present it with a sample case. The initial table is the following
one :

--------------------
drop table if exists test_gen ;

create table test_gen as
select *
from (select     chr((round(random()* 25) +65)::int)  as id    , random()* 100  as valfrom    generate_series(1,200) as
gorder    by id 
) as foo

select * from test_gen;
-------------------

What I want to do is to enumerate lines for each group of id, following
the order of val.

For example :
id    val            gen
A    2.65105138532817    1
A    38.9289360493422    2
A    74.6089164167643    3
B    2.01512188650668    1
B    11.4642047323287    2
B    31.2643219716847    3
B    65.8427979797125    4
C    0.759994331747293    1
C    11.8905796203762    2
C    13.7388648930937    3
C    49.1934351157397    4
C    83.1861903425306    5
D    45.8268967922777    1
D    57.1161589119583    2
E    9.72125697880983    1
E    61.324825277552    2
E    70.3348958399147    3
F    0.49891234234237    1


Here is the solution I ended up with :

---------------------------

-- first count number of ids per group
drop table test_gen2 ;

create table test_gen2 as
select t1.*, t2.nb
from test_gen as t1,(SELECT     id, count(*) as nb FROM     test_gen GROUP BY id) as t2
WHERE t1.id =t2.id
ORDER BY t1.id;

create sequence seq_test_gen start with 1;
create sequence seq_test_gen2 start with 1;

-- get the table with the order set (gen is our order)
select*
from(select    foo1.*,    nextval('seq_test_gen') as serialfrom (    select        *    from        test_gen2    order
by       id, val    ) as foo1) as t1,(select    foo.*,    nextval('seq_test_gen2') as serialfrom (    select
gb1.*,        generate_series(1, gb1.nb) as gen    from (        select            id, nb        from
test_gen2       group by            id, nb        ) as gb1     order by         gb1.id, gen    ) as foo) as t2 
wheret1.serial = t2.serial;
-----------------------------------

The problem seems to be as easy as : <sort my two sets and put them side
to side>. But I could not find a better way to do that than putting a
serial on left and right side and do a join on this serial.
I also tried to find a solution using a modulo but could not manage to
get it work.

Anybody for a ray of light on a different approach ? This look like a
recurrent problem, isn't there an experienced sql programmer here who
tackled this issued a couple of time ?

Thanks for any help,
Vincent