sql - Insert into temp values (select.... order by id)

Question

I'm using an Informix (Version 7.32) DB. On one operation I create a temp table with the ID of a regular table and a serial column (so I would have all the IDs from the regular table numbered continuously). But I want to insert the info from the regular table ordered by ID something like:

CREATE TEMP TABLE tempTable (id serial, folio int );

INSERT INTO tempTable(id,folio)
SELECT 0,folio FROM regularTable ORDER BY folio;

But this creates a syntax error (because of the ORDER BY)

Is there any way I can order the info then insert it to the tempTable?

UPDATE: The reason I want to do this is because the regular table has about 10,000 items and in a jsp file, it has to show every record, but it would take to long, so the real reason I want to do this is to paginate the output. This version of Informix doesn't have Limit nor Skip. I can't renumber the serial because is in a relationship, and this is the only solution we could get a fixed number of results on one page (for example 500 results per page). In the Regular table has skipped id's (called folio) because they have been deleted. if i were to put

SELECT * FROM regularTable WHERE folio BETWEEN X AND Y

I would get maybe 300 in one page, then 500 in the next page

Answer 1

You can do this by breaking up the SQL into two temp tables:

CREATE TEMP TABLE tempTable1 (
id serial,
folio int);

SELECT folio FROM regularTable ORDER BY folio
INTO TEMP tempTable2;

INSERT INTO tempTable1(id,folio) SELECT 0,folio FROM tempTable2;