user2904400 user2904400 - 2 months ago 6
SQL Question

Top ID For each record SQL

I have a number of different portfolio's, each with their own ID and along with that is the risk number with a run_id

What i am trying to do is for each date, pull the Bps number which is linked to the maximum run_id for each of the portfolios

select analysis_date,ptf_id,stat_name,Bps,run_id
from rpt.rm_Report_History
where analysis_date > '20160102' and criteria_Set= 'Daily'
and ptf_id in( '10038','10039')
and report_section_group = 'Key_Risk_Figures'
and rm_rcp_param_name = 'Fund'
and stat_class = 'standaloneVaR'


which gives the following output

output

Answer

here is the implementation using cte (applicable to sql server 2008 and later versions).

;with cte_1
as
(select analysis_date,ptf_id,stat_name,Bps,run_id,ROW_NUMBER() OVER(PARTITION BY ptf_id ORDER BY run_id desc) as RNO
from rpt.rm_Report_History 
where analysis_date > '20160102' and criteria_Set= 'Daily' 
and ptf_id in( '10038','10039')
and report_section_group = 'Key_Risk_Figures'
and rm_rcp_param_name = 'Fund'
and stat_class = 'standaloneVaR')

SELECT *
FROM cte_1
WHERE RNO=1