Re: 'order by' in an insert into command

Mike Nolan wrote:
> 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