Jarrod,

If you require an explicit ordering, explicitly use it in your order
by statement.

When you insert a record, it is inserted usually in storage order,
but not always. For example, if there is a deleted record that has
been garbage collected, then the new record might be inserted their.

It is the job of a select statement to return the records as quickly
as possible. If are not concerned by the order the records are
returned, then you would understandably get pretty annoyed if you had
to wait for Firebird to sort them.

In your particular case, you are using a left join which (in FB 1.5
anyway) the optimiser will decide to use the left table first, so it
will be executed in order of customer number first.

But you are explicity using an order by in your main query that does
not mention the ascending field, so Firebird could quite validly
return your data in whatever order it is convenient, providing the
orders.orderdate is correctly ordered.

Furthermore, even if Firebird does currently do things the way you
are thinking, in the future there may be a new optimisation possible
that mucks it up.

Even more risky, because you do not mention the customer number in
the query order by clause, another developer may not realise your
delicate implicit ordering and add another join to the table which
may change the plan.

If you want guaranteed, then you will need to return the customer
code from the stored procedure, and include it in your explicit order
by statement. Otherwise, it may work but it may also stop working at
some point in time.

Adam

--- In firebird-support@yahoogroups.com, "Jarrod Hollingworth"
<jarrod@b...> 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?
>
> 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?
>
> Regards,
>
> Jarrod Hollingworth
> Backslash
> jarrod@b...