At 01:44 PM 18/10/2005 -0400, you wrote:

>Hey all,
>
>Making this as sinple as possible... we have a table with a bunch of simple
>money transactions records. One of the fields we have in there is a called
>SortedBy, which is supplied by the customer of said transactions. Because the
>customer can supply literally anything for that field, it is defined as a
>varchar(10).
>
>I needed to gather some data for one of the customers, who only uses integers
>inside of the SortedBy field. Just trying to be nice and sort the data for the
>customer, I wrote a query that looked like this:
>
>Select SortedBy, sum(MyFees) as FeesTotal, sum(MyTaxes) as TaxesTotal
> >From MyTable
>Group By SortedBy
>Order By Cast(SortedBy as Integer);
>
>Now, without the Order By clause, I get back every bit of data I would expect.
>Adding the above order clause, it returns 0 records. Just to be clear,
>there was
>no error... just no records were returned. If I change the cast() to .. say
>varchar(200) or something, it runs fine. Is this a bug?
>
>Firebird 1.5.2 on Gentoo Linux.

You should be getting an exception here. You can't GROUP BY one ordering
of a column and then try to order that set by a different ordering of the
same column. (casting to any varchar would give the same ordering, not a
different one...)

Do you happen to be using the Fb 1.5.3 release candidate 2 and isql with
the -q switch? (It's become ultra-user-friendly - it doesn't deliver error
messages at all. :-> )

To fix the isql problem, go back to Fb 1.5.2 until the Fb 1.5.3
release. To fix the SQL problem, push the cast expression up into the
column selection list.

Select Cast(SortedBy as integer) as TotalGroup, sum(MyFees) as FeesTotal,
sum(MyTaxes) as TaxesTotal
From MyTable
Group By 1
Order By 1;

Now, unless you've got something else in the GROUP BY spec that's bending
the output order of the groups, you shouldn't need the ORDER BY
anyway....but you did mention that you'd simplified the example.

./heLen