'order by' in an insert into command

From: Mike Nolan <nolan(at)gw(dot)tssi(dot)com>
To: pgsql-general(at)postgresql(dot)org (pgsql general list)
Subject: 'order by' in an insert into command
Date: 2004-09-08 16:18:25
Message-ID: 200409081618.i88GIP38006094@gw.tssi.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ed L. 2004-09-08 16:23:14 8.0.0beta2: gcc: unrecognized option `-pthreads'
Previous Message gnari 2004-09-08 16:17:50 Re: Problems importing data from plain text file