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.