How to list the most recent record by date without having to aggregate all selected fields in SQL

Problem:

 Database table contains multiple rows for each "fund" with a different 'Effective Date'. I had to list the row with the most recent Effective Date. The most straight forward solution is to use a GROUP BY on the fund and SELECT max(Effective Date); however I also want to SELECT other columns from the table; we know that when there is a GROUP BY, we need to apply aggregation on each column we select, since one of the other column that I want selected has a numerical value, I had problems applying aggregation on it. Here is a solution that I learnt from someone, that works perfectly and is simple:

Solution

 Select the columns from the table you want to list and inner join this to another select statement for the same table  which lists the max(Effective Date) GROUP BY fund.

Here is the SQL in pseudo code:

SELECT t.fund,

            t. fund_org_code, 

            t.fund_manager_name, 

            t.fund_title, 

            t.fund_effective_date,

            t.fund_status

FROM fundTable t 

INNER JOIN (

                   SELECT max(fundTable.fund_effective_date) as effDate,

                               fundTable.fund as fundCode

                    FROM fundTable

                    GROUP BY fundTable.fund

                   ) t1

ON t.fund = t1.fundCode and t.fund_effective_date = t1.effDate

Note:

This solution maybe slower, however works for my purposes.