[GENERAL] Insert increment based on a group by?

[mike]

I am trying to do the following

I have a table with three relevant fields
subj, area, and no

What I want to do is insert a sequential number  by a group on the first
two fields eg:

values existing

sub     |       area    |       no
1       |       1       |       1
1       |       1       |       2
1       |       1       |       3
1       |       1       |       4
1       |       2       |       1
1       |       2       |       2
2       |       2       |       1
2       |       2       |       2

so if sub=2 and area=2 I would want to insert a value of 3 ie: max+1

So far I am getting an overall max or no insert.

this is my query

SELECT subj,area,  CASE WHEN max(file_no) IS NULL THEN '1' ELSE max
(file_no)+1 END FROM tb_files GROUP BY (subj||area),subj,area;

which produces max overall

any ideas appreciated

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]