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