Re: enumerate groups given a certain value

Hello, Picavet.

> 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 ?

Actually, I'm not very experienced in SQL. But from my point of view
this problem could be solved much more easily using plpgsql rather
than plain SQL.

Your initial query have been a little bit modified for convenience:
CREATE TABLE test_gen AS
SELECT *
FROM(SELECT  chr((round(random()* 25) +65)::int) AS id,  random()* 100 AS val,  0::INTEGER AS genFROM
generate_series(1,200)as gORDER BY id
 
) foo;

CREATE OR REPLACE FUNCTION enum_groups (varchar) RETURNS SETOF test_gen AS
$body$
DECLAREr chip.test_gen%ROWTYPE;_id VARCHAR;i INTEGER := 0;q TEXT;
BEGINq := 'SELECT * FROM ' || $1 || ' ORDER BY id ASC, val ASC;';FOR r IN EXECUTE q LOOP  IF ((_id IS NULL) OR (_id =
r.id))THEN    i := i + 1;  ELSE    i := 1;  END IF;  _id := r.id;  r.gen := i;  RETURN NEXT r;END LOOP;RETURN;
 
END;
$body$
LANGUAGE 'plpgsql';

Now you can SELECT * FROM enum_groups('test_gen') and you'll get following:

id      val     gen
B    2,35326588153839    1
B    11,4269167650491    2
B    11,9314394891262    3
B    27,9016905929893    4
B    28,548994101584    5
B    48,8151242025197    6
B    50,215089507401    7
B    59,613792411983    8
B    61,2281930632889    9
B    80,49540463835    10
C    5,86635880172253    1
C    11,5974457468838    2
C    15,8136531710625    3
C    29,8465201631188    4
C    52,9871591832489    5
C    57,3461000341922    6
C    63,3344274014235    7
...

HTH

PS. Sorry, I forget to reply all first time.
-- 
Best regards. Yuri.