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

>

How does this work logically, if you do not group by rowindex, then
you potentially have multiple rowindex values for a particular
date/description combination.

Then say you had this

RowIndex HierarchyDescription OrderDate OrderAmount
1 'row 1' 1/1/2005 100
2 'row 1' 2/2/2005 150
3 'row 1' 1/1/2005 200

When you sum it with your group by,

'row 1', 1/1/2005, 300
'row 1', 2/2/2005, 150

ordering by rowindex may do something but I can't see any logical
reason for doing so, because the first record is both before and
after the second record.

> 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 */
>

This actually looks different to your initial post. Your initial post
you were questioning about the ordering, and I could see that you
were going to need to change the stored procedure to return the
customer number, or at the very least return some incrementing value
that can be used in the join.

I did not really answer your problem (or at least I was not trying to
do that because it was obviously a simplified example). I was
pointing out that logically, if you want a records returned to you in
a specific order, you need to ask for it using the order by clause.
Even if the internal sorting happens to spit it out in the correct
order, if you do not explicitly say order by blah, then you can not
complain if the behaviour changes in a future version of Firebird,
and suddenly it returns it in reverse order or something.


Adam