表数据如下：
EventID | MPID | rundate | Horizon | otherData
1 | 1 | 23-Jun-2014 | 360 | other value
1 | 1 | 23-Jun-2014 | 365 | pther value
1 | 1 | 23-Jun-2014 | 300 | pther value
1 | 1 | 22-Jun-2014 | 700 | pther value
1 | 2 | 23-Jun-2014 | 400 | other value
1 | 2 | 23-Jun-2014 | 340 | oth
2 | 3 | 23-Jun-2014 | 360 | pther value
2 | 3 | 23-Jun-2014 | 300 | pther value
2 | 3 | 22-Jun-2014 | 365 | pther value
我想为每个事件和市场组选择最大运行日期，然后在该组中选择最大地平线，然后打印整行。
期望的结果是：
EventID | MPID | rundate | Horizon | otherData
1 | 1 | 23-Jun-2014 | 365 | pther value
1 | 2 | 23-Jun-2014 | 400 | other value
2 | 3 | 23-Jun-2014 | 360 | pther value
请让我知道这个的SQL查询。
我尝试了以下查询，但它不起作用：
SELECT * from dsie_result_overalls where id in (
SELECT k.id from dsie_result_overalls k,
(
SELECT a.event_id, a.marketplaceid, MAX(a.horizon) as horizon FROM dsie_result_overalls a,
(
SELECT id, event_id, marketplaceid, MAX(rundate) AS rundate FROM dsie_result_overalls
GROUP BY event_id, marketplaceid
) b
WHERE a.event_id = b.event_id AND a.marketplaceid = b.marketplaceid AND a.rundate = b.rundate
GROUP BY a.event_id, a.marketplaceid
) l WHERE k.event_id = l.event_id AND k.marketplaceid = l.marketplaceid AND k.horizon = l.horizon
);
它为max horizon选择多个运行日期。
最佳答案
尝试此查询
Select T.* From Tbl T JOIN
( Select Max(S.Horizon) MaxHorizon,Max(S.rundate) As dte,S.EventID,S.MPID
From Tbl S Join
( Select T1.EventID,Max(T1.rundate) As Maxrundate,T1.MPID
From Tbl T1 Group By T1.EventID,T1.MPID
) JR On S.rundate = JR.Maxrundate AND S.EventID = JR.EventID AND S.MPID = JR.MPID
Group By S.MPID,S.EventID
)R ON T.Horizon = R.MaxHorizon AND T.EventID = R.EventID AND T.MPID = R.MPID AND T.rundate = R.dte
Fiddle Demo
输出将是
EventID | MPID | rundate | Horizon | otherData
1 | 1 | 23-Jun-2014 | 365 | pther value
1 | 2 | 23-Jun-2014 | 400 | other value
2 | 3 | 23-Jun-2014 | 360 | pther value