Subject RE: [firebird-support] Guaranteed Row Order?
Author Jarrod Hollingworth
Hi Helen,

> >Are the results *guaranteed* to be in order of descending orderdate and
> >ascending customercode?
> No. In fact, theyre almost guaranteed *not* to be

OK, well that isn't going to work for me then.

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

That's what I meant by "sorting algorithm." For example, other than an index
the server has some algorithm to decide when, how and in what sequence to
change the order of two rows to meet something like "order 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.

In my example the stored proc explicitly sorts customers by name, this is
joined to some order dates and then this is sorted by descending date. Is
this result predictable? My question was asking this and the specific case
that the predicable result is that where two orderdates are the same the
order remains as ascending customers name. My guess would be that this is
how it would work but would depend on the sorting algorithm used (the
sequence in which rows are swapped could mess that up).

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

That's a good point. I cut my teeth on C-ISAM (an Informix low-level DB
structure) which I think stored them in primary key (record number?) order
but I have never made any assumptions about storage order.

As I mentioned in my reply to Adam I think that I have found a way to do
what I want. The crux of the problem is maintaining a hierarchical category
tree order whilst joining to another table and grouping to sum columns.

Thank you for your help.


Jarrod Hollingworth