Well, doesn't SQL do the query from the inside out?

IAC, my code is returning three results for many accounts. Excluding the subquery returns one per record, as intended. Here's the code for real:

SELECT     RMASTER.RMSACCTNUM, RMASTER.RMSSTATUS, RMASTER.ATTRNYCODE AS RmsRecvrcd, RPRDBAL.RMSOFFCRCD, RMASTER.RMSDATEASG, 
                      RFINANL.RMSTRANDTE AS RmsLastPmt, RFINANL.RMSTRANAMT AS LastPmtAmt, 
                      RPRDBAL.RMSCHGAMT - RPRDBAL.RMSRCVPCPL + RPRDBAL.RMSASSCCST - RPRDBAL.RMSRCVDCST + RPRDBAL.RMSACRDINT - RPRDBAL.RMSRCVDINT
                       + RPRDBAL.UDCCOSTS1 - RPRDBAL.UDCRECCS1 + RPRDBAL.UDCCOSTS2 - RPRDBAL.UDCRECCS2 + RPRDBAL.RMSCOST1 - RPRDBAL.RMSCOST1R
                       + RPRDBAL.RMSCOST2 - RPRDBAL.RMSCOST2R + RPRDBAL.RMSCOST3 - RPRDBAL.RMSCOST3R + RPRDBAL.RMSCOST4 - RPRDBAL.RMSCOST4R +
                       RPRDBAL.RMSCOST5 - RPRDBAL.RMSCOST5R + RPRDBAL.RMSCOST6 - RPRDBAL.RMSCOST6R + RPRDBAL.RMSCOST7 - RPRDBAL.RMSCOST7R + RPRDBAL.RMSCOST8
                       - RPRDBAL.RMSCOST8R + RPRDBAL.RMSCOST9 - RPRDBAL.RMSCOST9R + RPRDBAL.RMSCOST10 - RPRDBAL.RMSCOST10R - RPRDBAL.RMSXCSRCVS
                       AS RMSCurBal, RMASTER.RMSFILENUM
FROM         RMASTER INNER JOIN
                      RPRDBAL ON RMASTER.RMSFILENUM = RPRDBAL.RMSFILENUM LEFT OUTER JOIN
                      RFINANL ON RMASTER.RMSFILENUM = RFINANL.RMSFILENUM AND RFINANL.RMSTRANDTE * 1000000 + RFINANL.RMSTRANTIM =
                          (SELECT     MAX((m.rmsTranDte * 1000000) + m.rmsTranTim)
                            FROM          rFinAnl m
                            WHERE      m.rmsFileNum = rMaster.RmsFileNum AND (m.rmstrancde LIKE '5%' OR
                                                   m.rmstrancde LIKE '3%' OR
                                                   m.rmstrancde LIKE '4%') AND m.rmstranamt <> 0)
WHERE     (RMASTER.ATTRNYCODE = 'P013')
ORDER BY RMASTER.RMSACCTNUM

>Why not? It's called correlated subquery.

CREATE TABLE a (
>	acctnum int, officername varchar(30))
>
>CREATE TABLE  c (
>	acctnum int, datefield datetime, amountfield dec(10,2))
>
>INSERT INTO a VALUES(1, 'One')
>INSERT INTO a VALUES(2, 'Two')
>
>INSERT INTO c VALUES(1, getdate(), 1000)
>INSERT INTO c VALUES(1, getdate()-10, 5000)
>INSERT INTO c VALUES(1, getdate()+10, 3000)
>
>
>INSERT INTO c VALUES(2, getdate(), 4000)
>INSERT INTO c VALUES(2, getdate()-10, 5000)
>INSERT INTO c VALUES(2, getdate()+10, 6000)
>
>SELECT a.acctnum, a.officername,
>	c.datefield, c.amountfield
>FROM a
>INNER JOIN c ON a.acctnum = c.acctnum
>	AND c.datefield = (SELECT max(datefield) FROM c WHERE AcctNum=a.AcctNum)
>

>
>>
>>So, you think the inner SELECT can use a column from the outer select in it's WHERE? That would be unexpected...
>>
>>>Try

SELECT a.acctnum, a.officername,
>>>	b.anotherfield, b.anotherfield2,
>>>	c.datefield, c.amountfield
>>>FROM a
>>>INNER JOIN b on a.AcctNum = b.AcctNum
>>>INNER JOIN c ON a.acctnum = c.acctnum
>>>	AND c.datefield = (SELECT max(datefield) FROM c WHERE AcctNum=a.AcctNum)
>>>