Make your query unambiguous, Firebird refuses the possibility of
producing "random" results! Here's are three possibilities, the first
two will give you a slightly different result set, and the third is a
somewhat cumbersome approach to ascertain singular output that probably
can be simplified if you know your data:

select ar.app_status, app.app_status_desc, max(asco.app_status_order)
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 3

select ar.app_status, app.app_status_desc, asco.app_status_order
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, asco.app_status_order
order by asco.app_status_order

select ar.app_status, app.app_status_desc
from (cert_applicant_results ar
left join app_stat_code app
on ar.org_id=app.org_id
and ar.app_status=app.app_status)
left 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
and not exists(
select * from cert_applicant_results ar2
where ar2.app_status = ar.app_status
and ar2.org_id = ar.org_id
and ar2.jnum = ar.jnum
and ar2.certid = ar.certid
and ar2.id > ar.id
and (app.app_status_desc is null
or exists(
select * from app_stat_code app2
where ar2.org_id=app2.org_id
and ar2.app_status=app2.app_status
and app2.app_status_desc = app.app_status_desc)))
and not exists(
select * from app_stat_code app3
join cert_applicant_result ar3
on ar3.org_id=app3.org_id
and ar3.app_status=app3.app_status
where app3.app_status_desc = app.app_status_desc
and ar3.org_id = ar.org_id
and ar3.jnum = ar.jnum
and ar3.certid = ar.certid
and ar3.app_status = ar.app_status
and app3.id > app.id)
and not exists(
select * from app_stat_code_order asco2
where asco2.org_id = asco.org_id
and asco2.app_status = asco.app_status
and asco2.id > asco.id)
order by asco.app_status_order

Now, I'm not certain that I got this last query correct (three not
exists, one of which has a nested exists and another with a join, is
enough to confuse my head a bit with unknown data on a rainy morning). I
don't use dialect 1 myself, but I think I haven't used anything that
isn't available. Finally, I'll repeat: Firebird refuses ambiguity
(regardless of whether InterBase allows it or not).

Good luck in converting from InterBase to Firebird, as you've discovered
it is not simply to make a slight change in syntax, you also have to
remove logical errors from your code where InterBase has a more
pragmatic approach.

Set

Thomas Miller wrote:

> 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
>
>
> The error I am getting is complaining about the order by being outside
> the group by. Not a problem on IB. I should also mention this is
> Dialect 1. I am going to eventually convert everything to Dialect 3,
> but thought I would try to get the DB objects ported from IB d1 to FB d1
> before moving everything to d3.
>
> Thanks.