Sorry Thomas, to me this sounds like an ambiguous request, and Firebird
is pretty strict about rejecting possible ambiguity.

With a GROUP BY, I'd say you're telling the optimizer that fields not in
the group by are either aggregate fields (like sum or count) or
something to be ignored because they may vary. Theoretically, there
could be different values for asco.app_status_order with the same
ar.app_status and app.app_status_desc. Say you had the following
theoretical output:

ar.app_status app.app_status_desc (asco.app_status_order)
1 1 1 and 3
2 2 2

Which value of asco.app_status_order should the query sort by? Note that
it doesn't matter whether your data actually are ambiguous, just that
they theoretically could be so. One of the first things to remove from
Firebird when InterBase was open sourced was ambiguity, InterBase gave
results that sometimes were unpredictable.

The solution in Firebird is to remove the ambiguity, e.g. you could add
asco.app_status_order to the select and order by clause (using IBO you
could then hide this field from the user, and I suspect that is possible
if you don't use IBO as well). Mind you, if your data should happen to
be ambiguous, then you'll end up with what appears as duplicates to the
user. Another option may be to not use GROUP BY and rather add some NOT
EXISTS to avoid duplicates. Firebird has no problem with ORDER BY on
fields not in the output if you don't use any aggregation.

HTH,
Set

Thomas Miller wrote:

> The problem area is the Order By clause being outside the Group By.
>
> How do I get this working in FB? Thanks!
>
> select ar.app_status, app.app_status_desc
> from (cert_applicant_results ar left outer join app_stat_code app
> on ar.org_id=app.org_id and ar.app_status=app.app_status)
> left outer join app_stat_code_order asco on ar.org_id = asco.org_id
> and ar.app_status = asco.app_status
> where ar.org_id = :org_id
> and ar.jnum = :jnum
> and ar.certid = :certid
> group by ar.app_status, app.app_status_desc
> order by asco.app_status_order ascending