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% |
CodePudding user response：
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
CodePudding user response：
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