SubQuery Problem (1 reply)

I'm having trouble with the following query, if only one result is returned it executes in a second or two, however if there are multiple reuslts it errors out. I also tried to us = ANY and = ALL and the query runs endlessly. Any suggestions?

SELECT t1.DATE, t1.REASON, t1.TITLE, Left( t1.REASON, 8 ) AS CLAIM_NO, Right( t1.REASON, 2 ) AS STAGE, t1.ID AS transatn_ID, t1.CLAIM, o1.ID AS ORDER_ID, o1.SUBREF, t1.SPOOL_ORD

FROM order_ln o1

INNER JOIN transatn t1 ON o1.ID = t1.TRANS_REF

WHERE

t1.ID =

( SELECT max( t1.ID ) AS ID

FROM order_ln o1

INNER JOIN transatn t1 ON o1.ID = t1.TRANS_REF

WHERE t1.DATA_TYPE = 'CM'

AND t1.`DATABASE` = 'CLAIM '

AND t1.REASON LIKE '%'

AND t1.TRANS_REF =2000563600

GROUP BY left( REASON, 8 )

)