top ID每条记录的SQL

Question

我有许多不同的投资组合，每个都有自己的ID，以及带有run_id的风险编号

我要做的是为每个日期，拉的Bps数字链接到每个投资组合的最大run_id

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'

给出如下输出

输出

Answer 1

这是一个具有子选择的解决方案，该子选择首先确定列表中的最大run_id，然后选择具有标准和最大run_id的所有行。由于列不是唯一的，您可以使用GROUP BY修饰符去除多个匹配项。

这是一个典型的最小/最大群问题。有些DBMS有更好的解决方案，但是下面的这个应该可以在大多数系统中工作。

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'
and run_id = (SELECT max(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')
GROUP BY run_id

编辑:一些DBMS将要求您按所有选定的列分组:

...
GROUP BY analysis_date,ptf_id,stat_name,Bps,run_id

Answer 2

下面是使用cte的实现(适用于SQL server 2008及更高版本)

;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

Answer 3

我假设你所说的tsql是指SQL server…在这种情况下，抛出row_number()——实际上是我最喜欢的SQL工具

select A1.*
from
(
select analysis_date,ptf_id,stat_name,Bps,run_id, row_number() over(partition by ptf_id order by run_id desc) as Run_Order
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' ) A1
where A1.Run_Order = 1