At 10:01 AM 22/09/2005 +1000, you wrote:

>If I select rows from a stored proc and join to another table and only order
>the result by a column in that table is the sub-order of the rows returned
>from the stored proc guaranteed to be in the original order?
>
>(Contrived example follows to show what I'm talking about)
>
>e.g. If I have a stored proc returning rows in a specific order:
>
>customers table:
>id name customercode
>1 Smith 333
>2 Jones 111
>3 Black 222
>
>create procedure get_customers
>returns (
> id integer,
> name varchar(20) )
>as
> for
> select id, name
> from customers
> order by customercode
> into :id, :name
> do
> suspend;
>end
>
>e.g. If I have a query which selects from this stored procedure and joins to
>another table, ordering *only* by a column from that table:
>
>orders table:
>orderdate customerid
>2005-09-01 3
>2005-09-01 2
>2005-09-02 1
>2005-09-02 3
>2005-09-01 1
>2005-09-02 2
>
>select
> cust.name, orders.orderdate
>from get_customers cust
> left join orders on
> cust.id = orders.customerid
>order by orders.orderdate desc
>
>Are the results *guaranteed* to be in order of descending orderdate and
>ascending customercode?

No. In fact, theyre almost guaranteed *not* to be

>2005-09-02 Jones
>2005-09-02 Black
>2005-09-02 Smith
>2005-09-01 Jones
>2005-09-01 Black
>2005-09-01 Smith
>
>Or is the sub-order on customercode different (possibly random) due to the
>sorting algorithm used in the server?

There is no "sorting algorithm used in the server", other than what is
applied as the result of a sorting specification in the select statement -
order by or group by. If you don't specify a column in a sorting
specification, it will just come out according to the composition of the
set once any specified sorting instructions have been applied.

If you cut your database teeth on an ISAM-style dbms like Paradox or
Access, you might be under the impression that indexes affect the order in
which records are stored in tables. They don't. You can add and remove
indexes without the tiniest effect on storage order. They just continue to
lie there, waiting to respond to a query specification.

Although the engine insists on restricting a data page to store only
records for a single table, it doesn't need to store the data pages for
that table contiguously. After a while, data pages for a particular table
could be distributed anywhere on the disk. A restore will get them onto
contiguous pages...for a while...

./heLen