Hi Adam,

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

Unfortunately in my case I can't solve it that way. I omitted my 'real'
problem and chose to focus on one of the possible solutions.

My 'real' problem is that I need to maintain the order of rows returned from
a stored procedure where the ordering is determined by a column which has a
unique value for each row, yet group by a second column ignoring the unique
ordering index column.

e.g. I want a "summary by date by category" result. The categories come from
a hierarchical category tree and I want the order of the tree maintained.

select
c.rowindex,
c.hierarchydescription,
orders.orderdate,
sum(orders.orderamount)
from category_tree c
left join orders on
c.id = orders.categoryid
group by
c.rowindex, /* I DON'T WANT THIS */
orders.orderdate,
c.hierarchydescription
order by
orders.orderdate,
c.rowindex /* I WANT THIS */

I want to group by orderdate and hierarchydescription (e.g. Manufacturing /
Ford / NY Engine Plant) only, yet maintain the sub-order of the categories
as per the rowindex, not the hierarchydescription as the
hierarchydescription can also contain a category code which messes up the
sort order).

I'm thinking now that I need to add a new return column from the stored
procedure that is incremental to provide the sort order but returns the same
value for the same hierarchydescription. It is hard to explain the
complexities of what is involved but the stored proc can be set to only
return a limited depth of the hierarchydescription yet the other full unique
details of the category meaning that two rows returned can have different
category id's and rowindex but the same hierearchydescription.

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

I thought that there might be a SQL standard stating exactly how the result
set is ordered. In my contrived example I had two explicit orderings, one
within the stored proc and one in the main query.

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

As mentioned above that won't help my particular problem. Any ideas?

Regards,
Jarrod Hollingworth