mysql - 查询查找具有最大运行日期和最大范围的结果

Question

表格数据如下:

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
);

它为最大范围选择多个运行。

Answer 1

试试这个查询

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