Re: 'order by' in an insert into command

Try:

insert into pending_tnmt_sec
select tseceventid, tsecsecno,
nextval('sec_seq'),
tsecrtddt
from (
   select tseceventid, tsecsecno, tsecrtddt
   from tnmtsec
   order by tsecrtddt,tseceventid,tsecsecno) as ss;




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:
>
> tsecrtddt        tseceventid   tsecsecno      seq
>
> 2004-08-30     | 20040731910 |         1 | 356270    ### out of sequence
> 2004-07-08     | 20040531897 |         2 | 360792
> 2004-06-03     | 20040425023 |         1 | 354394
> 2004-04-23     | 20040320702 |         1 | 353557
> 2004-02-18     | 20040117178 |         2 | 359387    ### out of sequence
> 2004-01-10     | 20031213418 |         1 | 351315
>
> I can't tell whether this is because the order by clause in the insert
> is being ignored or because the sequence is incrememted before the sort
> takes place.  Is there a way to do this insert?
> --
> Mike Nolan
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org
>