Question 113188 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Obtain latest data for each product

I have a table that collects securities holdings data for Funds like below.

How do I extract latest data for each FundName?

EffectiveDate FundName SecurityName PercentageOfAssets
30/06/2022 Fund A xxx 33.33%
30/06/2022 Fund A yyy 33.33%
30/06/2022 Fund A zzz 33.33%
31/07/2022 Fund B xxx 50%
31/07/2022 Fund B yyy 50%
31/08/2022 Fund B yyy 50%
31/08/2022 Fund B zzz 50%
31/07/2022 Fund A xxx 50%
31/07/2022 Fund A yyy 50%

What I’m expecting

EffectiveDate FundName SecurityName PercentageOfAssets
31/07/2022 Fund A xxx 50%
31/07/2022 Fund A yyy 50%
31/08/2022 Fund B yyy 50%
31/08/2022 Fund B zzz 50%
Tags:

3

Answers


  1. 0

    This will get the expected output

    ;WITH CTE AS (    
    SELECT EffectiveDate, FundName, SecurityName, PercentageOfAssets
        , ROW_NUMBER() OVER (PARTITION BY FundName ORDER BY EffectiveDate DESC) AS RowNum
)    
SELECT *
FROM CTE
WHERE RowNum = 1
    Login or Signup to reply.
  2. 0

    You can do it with cross apply in SQL Server:

    select t1.EffectiveDate, t1.FundName, t1.SecurityName, t1.PercentageOfAssets
from @tbl t1
cross apply (
    select t2.FundName, max(t2.EffectiveDate) maxEffDate
    from @tbl t2
    group by t2.FundName
) latest
where latest.FundName = t1.FundName
    and latest.maxEffDate = t1.EffectiveDate
order by t1.EffectiveDate
    Login or Signup to reply.
  3. 0

    Create a subquery that will get the MAX EffectiveDate then JOIN it to an outer query to return the rest of your rows:

    SELECT b.MaxDate, a.FundName, a.SecurityName, a.PercentageOfAssets
FROM (
      SELECT FundName, MAX(EffectiveDate) as MaxDate
      FROM Funds
      GROUP BY FundName
) b
INNER JOIN Funds a
ON a.FundName = b.FundName
  AND a.EffectiveDate = b.MaxDate
ORDER BY a.FundName, a.SecurityName ASC

    Result:

    | MaxDate    | FundName | SecurityName | PercentageOfAssets |
|------------|----------|--------------|--------------------|
| 31/07/2022 | Fund A   | xxx          | 50%                |
| 31/07/2022 | Fund A   | yyy          | 50%                |
| 31/08/2022 | Fund B   | yyy          | 50%                |
| 31/08/2022 | Fund B   | zzz          | 50%                |

    Fiddle here.

    Note: This will work for both MySQL and SQL Server.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search