Insert increment based on a group by? - Mailing list pgsql-general

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