Thread: Insert increment based on a group by?

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

On Fri, Jun 04, 2004 at 11:39:53 +0100,
  mike <mike@bristolreccc.co.uk> wrote:
>
> 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;

You should do a lock table in exclusive mode before doing this, unless
you know nothing else is going on.

An insert would like like:
INSERT INTO table_name (sub, area, file_no) select 'sub_value', 'area_value',
  (SELECT max(file_no)+1 FROM table_name where sub = 'sub_value' AND
    area = 'area_value')

If you have an index on either area, sub, file_no or sub, area, file_no,
you can rewrite the subselect to use ORDER BY and LIMIT.

It might be better to just assign unique values of file_no for the whole
table. You can use a sequence to support that. That would allow you
to support more concurrency.