Insert increment based on a group by?

From: mike <mike(at)bristolreccc(dot)co(dot)uk>
To: pgsql-general(at)postgresql(dot)org
Subject: Insert increment based on a group by?
Date: 2004-06-04 10:39:53
Message-ID: 1086345593.28023.24.camel@datacc
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jan Wieck 2004-06-04 11:50:19 Re: [HACKERS] Slony-I goes BETA
Previous Message Nagib Abi Fadel 2004-06-04 09:23:30 Re: Creating a session variable in Postgres