At 05:03 PM 19/01/2005 +1300, you wrote:

>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.

Yup. SELECT FIRST 1 of course (as you assumed) doesn't make sense without
an order by clause. Even if an order by clause were valid in a subquery
(which, by design, is scalar, makes ORDER BY illogical) SELECT n returns
the whole set *first*, then discards all but the row(s) specified, i.e. it
is about the worst thing you could use in a subquery.

>
>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

Do this:

select
ess1.CAMPAIGN_NAME,
(
SELECT max(elsa.EE_SP_APPROVAL.APPROVAL_TYPE)
FROM EE_LNK_SHEDULE_APPROVAL elsa
LEFT JOIN EE_SP_APPROVAL esa ON
elsa.EE_SP_APPROVAL_ID = esa.ID
where elsa.EE_SP_SCHEDULE_ID = ess1.ID)
as APPROVAL_TYPE
from
EE_SP_SCHEDULE EE_SP_SCHEDULE1 ess1

Create a descending index on elsa.EE_SP_APPROVAL.APPROVAL_TYPE.
If EE_SP_APPROVAL.APPROVAL_TYPE has a lot of values well distributed
through the table, index the column on its own; otherwise make a
descending composite index on it (leftmost) with a highly selective column
(the PK of elsa would be best.)

./helen