SQL Insert with Value & MAX?

Given a table with columns A and B, the combination {A,B} being the primary key. Given rows of {X,1}, {X,2}, and {X,3}, I'd like to INSERT the row {X,<Max(B)+1}. My question: is this possible in a single SQL statement, or must the MAX(B) WHERE A=X be saved off in a variable first, and INSERTed into the table (plus one)?

When a statement such as INSERT INTO ThisTable VALUES (X, MAX(B) WHERE A=X) is tried, the error GROUP FUNCTION (MAX) NOT ALLOWED HERE (or some such) is returned. And while you can use a subquery in an INSERT statement, it appears as if you can't do so in conjunction with a VALUES clause.

Anyway: 1) I'd still like to be able to do this in a single statement, and; 2) thanks for any help (to a SQL rookie).

Bill Pollack, Schodack, NY Received on Wed Mar 08 1995 - 04:53:07 CET