SQL Server: problems with max and IN clause

I have this table in SQL Server 2008:

create table [hFUNDASSET] 
(
    [hFundAssetID] numeric(19,0) identity not null, 
    [fundAssetID] numeric(19,0) not null, 
    [modified] datetime not null, 
    primary key ([hFundAssetID])
);

Its a history table, the goal is to get the closest hFundAssetID based on a given max modified column timestamp, for every distinct fundAssetID.

The following query gets the correct latest modified for each fundAssetID:

select max(modified), fundAssetID 
    from hFundAsset 
 where fundAssetID IN 
    (
        select distinct (fundAssetID) 
            from hFundAsset where modified < 'April 20, 2010 11:13:00'
    )
group by fundAssetID;

I can't put the hFundAssetID in the select clause without it being in the group by , which would return extra rows. Somehow, I need the hFundAssetID matching each one of the modified, fundAssetID p开发者_如何学编程airs returned in the above query, or equivalent. But SQL Server doesn't allow multiple values for the IN clause, according to their docs:

"subquery - Is a subquery that has a result set of one column. This column must have the same data type as test_expression."

Googling shows that 'exists' and joins are typically used with mssql in these cases, but I've tried that using 'max' and 'group by' and I'm having problems getting it to work. Any help appreciated.

Try this:

WITH qry AS
(
    SELECT a.*,
            RANK() OVER(PARTITION BY fundAssetID ORDER BY modified DESC) rnk
        FROM hFundAsset a
     WHERE modified < 'April 20, 2010 11:13:00' 
)   
SELECT *
  FROM qry
WHERE rnk = 1