Martijn Tonies wrote:

>> So? you create the table and then INSERT from a SELECT?
>> If I do that I can't insert it ordered because the insert does not allow
> the ORDER BY in the >INSERT
>
> Please explain the point of having an ordered insert?

I don't know what his points are, but occationally it is sensible to
order a set for later retrieval. E.g. suppose you had a table containing
information like (fictional data):

PK Parent Child Birthdate
1 Ann Jim 25.2.1981
2 Helen Sparky 1.1.2003
3 Ann Inter 1.4.1983
4 Ann Base 1.4.1983

Then you had to produce a flat file for each parent. Of course, you
could do:

SELECT R1.PARENT, R1.CHILD, R2.CHILD, R3.CHILD
FROM RELATIONSHIPS R1
LEFT JOIN RELATIONSHIPS R2
ON R1.PARENT = R2.PARENT
AND (R1.BIRTHDATE < R2.BIRTHDATE
OR (R1.BIRTHDATE = R2.BIRTHDATE
AND R1.PK < R2.PK))
AND NOT EXISTS(SELECT * FROM RELATIONSHIPS R3
WHERE R1.PARENT = R3.PARENT
AND (R1.BIRTHDATE < R3.BIRTHDATE
OR (R1.BIRTHDATE = R3.BIRTHDATE
AND R1.PK < R3.PK))
AND (R2.BIRTHDATE > R3.BIRTHDATE
OR (R2.BIRTHDATE = R3.BIRTHDATE
AND R2.PK > R3.PK))
LEFT JOIN RELATIONSHIPS R4...

(yes, I have written similar SQL)

Imagine how much simpler it would be to create an additional table with
a new PK field filled using a trigger with a generator and using:

INSERT INTO AdditionalTable(Parent, Child, Birthdate)
SELECT PARENT, CHILD, BIRTHDATE
FROM RELATIONSHIPS
ORDER BY 1, 2

Then the select would simply become

SELECT R1.PARENT, R1.CHILD, R2.CHILD, R3.CHILD
FROM ADDITIONALTABLE R1
LEFT JOIN ADDITIONALTABLE R2
ON R1.PARENT = R2.PARENT
AND R1.PK+1 = R2.PK
LEFT JOIN ADDITIONALTABLE R3 ...

Admittedly, this is an extreme case and it isn't difficult to solve this
problem without ordered inserts without having to use complex sql
(introducing a field CHILD_NO and use a few update statements would
achieve the same thing). But it shows a situation where an ordered
insert would be a quick and intuitive way to solve the problem.

Set