How to use limit in subquery?

I need to write a single query to get the results for the below cases.

First I need to get the 'Transfer_Request_ID' from the below query, which will give more results. As the request id is auto generated, we will not know what number it would be. so need to take based on current date by ordering in descending order. So getting the latest Top 1 would be my expected Request ID.

select  Transfer_Request_ID, * --top 1 *
from    TRANSFER_REQUEST  
where   From_Account_ID = 414
and     To_Account_ID = 20765
and     Processing_End_Date > '9999'
--and     Transfer_Request_ID = 5345 // This ID is auto-generated, so cant put in where clause until we know it
order by Processing_Start_Date desc

After that I need to pass it to below query to get the list of its own transaction statuses, which will give list of all the statuses (more than 1 results is expected)

select  *
from    STATUS s,
        TRANSFER_REQUEST_STATUS tfrs
where   tfrs.Transfer_Request_ID = 5345
and     s.Status_ID = tfrs.Status_ID

I need to combine and use as single query to get the data, as in my test script it is better to have one instead of multiple. Any help here.

I tried a subquery option, but it gives me error like Incorrect syntax near the keyword 'top'.

select  *
from    STATUS s,
        TRANSFER_REQUEST_STATUS tfrs
where   s.Status_ID = tfrs.Status_ID
and     tfrs.Transfer_Request_ID in (
                                select  top 1 Transfer_Request_ID
                                from    TRANSFER_REQUEST t
                                where   From_Account_ID = 414
                                and     To_Account_ID = 20765
                                and     Processing_End_Date > '9999'
                                order by Processing_Start_Date desc
                            )

UPDATED with solution idea from @markp-fuso.

select  tfr.Transfer_Request_ID, tfr.From_Account_ID, tfr.From_Account_Type, tfr.To_Account_ID, tfr.To_Account_Type, tfr.Amount, s.Status_ID, s.Value, s.Description, tfr.*
from    TRANSFER_REQUEST tfr,
        TRANSFER_REQUEST_STATUS tfrs,
        STATUS s
where   tfr.Transfer_Request_ID = (SELECT MAX(Transfer_Request_ID) FROM TRANSFER_REQUEST)
and     tfr.From_Account_ID = 414
and     tfr.To_Account_ID = 20765
and     tfr.Processing_End_Date > '9999'
and     tfrs.Transfer_Request_ID = tfr.Transfer_Request_ID
and     s.Status_ID = tfrs.Status_ID
order by tfr.Processing_Start_Date desc

Solution

Assuming Sybase ASE:

the top # clause is only supported in derived tables (ie, it's not supported in sub-queries as in OP's sample)
the order by clause is not supported in sub-queries

If I'm reading the query correctly, OP wants the Transfer_Request_ID for the record with the max(Processing_Start_Date).

One idea:

select *
from   STATUS s,
       TRANSFER_REQUEST_STATUS tfrs
where  tfrs.Status_ID = s.Status_ID
and    tfrs.Transfer_Request_ID = (select tr1.Transfer_Request_ID  -- see NOTES !!!!
                                    from  TRANSFER_REQUEST  tr1
                                    where tr1.From_Account_ID     = 414
                                    and   tr1.To_Account_ID       = 20765
                                    and   tr1.Processing_End_Date > '9999'
                                    and   tr1.Processing_Start_Date = (select max(tr2.Processing_Start_Date)
                                                                       from   TRANSFER_REQUEST tr2
                                                                       where  tr2.From_Account_ID     = tr1.From_Account_ID
                                                                       and    tr2.To_Account_ID       = tr1.To_Account_ID
                                                                       and    tr2.Processing_End_Date > '9999' ) )

NOTES:

this assumes the tr1 --> tr2 join only finds one row where tr1.Processing_Start_Date = max(tr2.Processing_Start_Date)
if the join finds more than 1 matching row then OP needs to tell us what to do ...
Option #1: pick only one matching row, but based on what criteria ??
Option #2: modify the top-most query to process a list of Transfer_Request_ID's (ie, replace the = with a in => trfs.Transfer_Request_ID in (select tr1.Transfer_Request_ID ...) ??
Option #3: ??
Sybase ASE does not support CTEs; I think the Sybase SQLAnyehere and Sybase IQ products do support CTEs so OP could certainly rewrite the two sub-queries as CTEs

The query was tested and verified on a Sybase ASE 16.0 SP04 PL04 dataserver.

We're not given any table definitions so I guessed at the following based on OP's join and where clauses:

create table TRANSFER_REQUEST
(Transfer_Request_ID    int
,From_Account_ID        int
,To_Account_ID          int
,Processing_Start_Date  varchar(10)
,Processing_End_Date    varchar(10)
)
go

create table TRANSFER_REQUEST_STATUS
(Transfer_Request_ID    int
,Status_ID              int
)
go

create table STATUS
(Status_ID              int
)
go