Sql Server double subquery

I have a table which is kinda like an historic table... so I have data like this

idA numberMov FinalDate

1 10 20090209

2 14 20090304

1 12 20090304

3 54 20080508

4 42 20090510

... ... ....

I need to retrieve the numberMov based on the newest finalDate from each idA so I use this

select a.numberMov from (select idA, max(finalDate) maxDate from table1 group by idA) as b inner join table1 a on a.idA=b.idA and a.finalDate = b.maxDate

Now I have another query like this

select m fields from n tables where n5.numberMov in ("insert first query here")

I feel like there is a b开发者_开发问答etter solution but can't think of any, I really dont like having two subqueries in there.

Any suggestions?

Not enough information to test it myself but something like this might work.

select m fields
from a inner join
 (select numberMov,
  max(FinalDate) as maxDate
 from a
 group by numberMov) b 
  on a.numberMov = b.numberMov
  and a.FinalDate = b.maxDate inner join
 n tables on a.numberMov = n.numberMov

You don't say which edition of SQL server, but this will work in SQL 2005+

;WITH rankCTE
AS
(
    SELECT idA
           ,numberMov
           ,FinalDate
           ,ROW_NUMBER() OVER (PARTITION BY idA
                               ORDER BY FinalDate DESC
                              ) AS rn
    FROM table1
)
,latestCTE
AS 
(
    SELECT idA
           ,numberMov
           ,FinalDate
    FROM rankCTE
    WHERE rn = 1
)
SELECT m fields 
FROM n tables 
WHERE n5.numberMov IN (SELECT numberMov FROM latestCTE)