Subject Guaranteed Row Order?
Author Jarrod Hollingworth
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) )
select id, name
from customers
order by customercode
into :id, :name

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, orders.orderdate
from get_customers cust
left join orders on = 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?


Jarrod Hollingworth