Set,

I always love your SQL solutions, but...

> >> 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

8<--

> 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.

What you're doing is assigning a meaning to an artificial generated primary
key value.

This, IMO, is bad design.

Martijn Tonies
Database Workbench - development tool for Firebird and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com