I have the following insert to populate a new table:
insert into pending_tnmt_sec select tseceventid, tsecsecno, nextval('sec_seq'), tsecrtddt from tnmtsec order by tsecrtddt,tseceventid,tsecsecno;
I need to access this data in a particular order which may change over time but the initial order I want is in the order by clause.
The problem is, I'm not getting the data into the right order based on the sequence values being inserted:
In your example, I would expect the nextval() to be called during the "fetch", before the ordering. You could probably do something like:
INSERT INTO pending_tnmt_sec
SELECT foo.*, nextval('sec_seq') FROM
(
SELECT tseceventid, ...
ORDER BY tsecrtddt,tseceventid,tsecsecno
) AS foo
;I'm not sure whether the SQL standard requires the ORDER BY to be processed in the sub-select. From a relational viewpoint, I suppose you could argue that ordering is strictly an output feature.
-- Richard Huxton Archonet Ltd
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
