New Id usign max(id)

From: mike <hillmw_at_charter.net>
Date: 6 Oct 2005 13:39:05 -0700
Message-ID: <1128631145.610730.185020@g49g2000cwa.googlegroups.com>

I have a table defined like:

CREATE TABLE mytable
(

NUM		INTEGER	NOT NULL,
ID		INTEGER	NOT NULL,
TITLE		VARCHAR(50) NOT NULL,

.
. (other fields)
.

)

ALTER TABLE mytable
ADD CONSTRAINT XPK_IDX PRIMARY KEY(NUM, ID) ALTER TABLE mytable

	ADD CONSTRAINT XFK FOREIGN KEY (NUM)
		REFERENCES yourtable(NUM)
		ON DELETE CASCADE

So my NUM, ID relationship would look like:

NUM ID
1 1
1 2
1 3
2 1
2 2
2 3

I wanted the ID to be sequential, but can't tie a sequence to it because for each NUM I'd like the ID to be 1,2,3, etc. as defined

So, my question then is before I do the insert are there any other ways you consider better than doing a query to get the max(id).

My select would be: select max(id) from mytable where num=1

Mike Received on Thu Oct 06 2005 - 15:39:05 CDT