Hi,

I have a query that pulls back two fields, 1 of these fields is a sub
select 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 /*WORKS IF
TAKEN OUT*/
) APPROVAL_TYPE
from
EE_SP_SCHEDULE EE_SP_SCHEDULE1

=========================================================
This e-mail has been scanned for Viruses and Content and cleared by CommArc Cube Server


[Non-text portions of this message have been removed]