Hi Martin!

There are a couple of things that I do not like with your statement.
One of them is that you in your subselect uses a left outer join.
Outer joins are more complicated than inner joins, and I think your
query may be rewritten without any outer join (see below).

Also, I dislike using FIRST within a subselect - even though I cannot
give any good explanation why (other than first requiring a sort of
the entire subselect, but I do not know how expensive this is in terms
of processing).

This is how I would have written your select:

select
CAMPAIGN_NAME,
(SELECT ESA1.APPROVAL_TYPE
FROM EE_LNK_SHEDULE_APPROVAL ELSA1
JOIN EE_SP_APPROVAL ESA1 ON
ELSA1.EE_SP_APPROVAL_ID = ESA1.ID
WHERE ELSA1.EE_SP_SCHEDULE_ID = EE_SP_SCHEDULE1.ID
AND NOT EXISTS(SELECT *
FROM EE_LNK_SHEDULE_APPROVAL ELSA2
WHERE ELSA2.EE_SP_SCHEDULE_ID = EE_SP_SCHEDULE1.ID
AND ELSA2.CREATED > ELSA1.CREATED)) APPROVAL_TYPE
FROM EE_SP_SCHEDULE EE_SP_SCHEDULE1

For me, this is also simpler to read than your original select, but
that may just be because of my own preferences when writing selects.

HTH,
Set

--- In firebird-support@yahoogroups.com, "Martin Catherall" wrote:
> Hi,
>
> I have a query that pulls back two fields, 1 of these fields is a
> subselect which would normally not be a problem.
> However the subselect basically "selects the latest record" so I
> only need to select the top record which means that I simply add a
> "first 1" clause to firebird statement.
> However this select the first record based on an ASC sort order and
> I would like this based on a DESC sort order, so I put an order by
> clause in the subselect, which does not work.
> That the order by clause in the sub select out and the query works.
>
> Can anybody please help me out.
>
> Cheers
>
> Martin.
>
> select
> CAMPAIGN_NAME,
> (
> SELECT
> first 1 EE_SP_APPROVAL.APPROVAL_TYPE
> FROM
> EE_LNK_SHEDULE_APPROVAL
> LEFT OUTER JOIN EE_SP_APPROVAL ON
> (EE_LNK_SHEDULE_APPROVAL.EE_SP_APPROVAL_ID = EE_SP_APPROVAL.ID)
> WHERE
> (
> (EE_LNK_SHEDULE_APPROVAL.EE_SP_SCHEDULE_ID =
> EE_SP_SCHEDULE1.ID)
> )
> ORDER BY EE_LNK_SHEDULE_APPROVAL.created desc
> ) APPROVAL_TYPE
> from
> EE_SP_SCHEDULE EE_SP_SCHEDULE1