SQL – Convert ROW_NUMBER function with multiple order by

I am trying to convert this query to a subquery without the ROW_NUMBER function:

 SELECT InvestorFundId, AsOfDate, AddedOn FROM (
   SELECT ROW_NUMBER() OVER (PARTITION BY InvestorFundId ORDER BY AsOfDate DESC, AddedOn DESC) AS HistoryIndex, *
                      FROM   [Investor.Fund.History]
                      WHERE  DataStatusId = 1 AND AsOfDate <= (SELECT PeriodDate FROM [Fund.Period] WHERE Id = 5461)
                      ) G WHERE HISTORYINDEX = 1

Basically this is selecting the most recent [Investor.Fund.History] within a time period and depending on the status.

So far I have this:

SELECT InvestorFundId, MAX(AsOfDate) AS MaxAsOfDate, MAX(AddedOn) AS MaxAddedOn FROM [Investor.Fund.History]
WHERE DataStatusId = 1 AND AsOfDate <= (SELECT PeriodDate FROM [Fund.Period] WHERE Id = 5461)
GROUP BY InvestorFundId

My query gives the incorrect results and it does this because when i use the MAX function on multiple columns, it does not select the max based on the order of both columns like the ROW_NUMBER does, instead it selects the MAX no matter both columns order positions.

For example, if I have a subset of data which looks like this:

 | InvestorFundId |          AsOfDate       |           AddedOn         |
 |  1             | 2010-10-01 00:00:00.000 |   2012-04-18 09:29:33.277 |
 |  1             | 2006-11-01 00:00:00.000 |   2013-04-18 11:25:23.033 |

The ROW_NUMBER function will return the following:

  |  1             | 2010-10-01 00:00:00.000 |   2012-04-18 09:29:33.277 |

Whereas my function returns this:

 |  1             | 2010-10-01 00:00:00.000 |   2013-04-18 11:25:23.033 |

Which as you can see, is not actually a row in the table.

I would like my function to correctly return the row in the table based on the MAX AsOfDATE AND AddedOn

Can anyone help?

Answer

If you have a unique id that identifies each row, then you can do:

WITH ifh as (
      SELECT InvestorFundId, AsOfDate, AddedOn
      FROM [Investor.Fund.History]
      WHERE DataStatusId = 1 AND AsOfDate <= (SELECT PeriodDate FROM [Fund.Period] WHERE Id = 5461)
     )
SELECT ifh.*
FROM ifh
WHERE ifh.? = (SELECT ?
               FROM ifh ifh2
               WHERE ifh2.InvestorFundId = ifh.InvestorFundId
               ORDER BY AsOfDate DESC, AddedOn DESC
               FETCH FIRST 1 ROW ONLY
              );

The ? is for the column that uniquely identifies each row.

This is also possible to do using APPLY:

select ifh2.*
from (select distinct InvestorFundId
      from ifh
     ) i cross apply
     (select top (1) ifh2.*
      from ifh ifh2
      where fh2.InvestorFundId = i.InvestorFundId
      order by AsOfDate DESC, AddedOn DESC
      fetch first 1 row only
     ) ifh2;