Re: Insert increment based on a group by?

On Fri, Jun 04, 2004 at 11:39:53 +0100,
mike <mike(at)bristolreccc(dot)co(dot)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.