我在groupby中见过许多“n值”，但我需要它两次。我有：

CREATE TABLE foo(PK INT(11) NOT NULL, Delivery_Date DATE, Hour_Ending TIME(0), 
                     Resource VARCHAR(26), Rate FLOAT, Submit_Time DATETIME(0), Eff_stop DATETIME(0),  PRIMARY KEY (PK));

insert into foo values(1,'2017-01-04','07:00:00','Plant_BAR','10','2017-01-03 05:00:00','2017-01-03 06:22:00'),
(2,'2017-01-04','07:00:00','Plant_BAR','9','2017-01-03 06:00:00','2017-01-03 06:55:00'),
(3,'2017-01-04','07:00:00','Plant_BAR','10','2017-01-03 06:00:00','2017-01-03 08:22:00'),
(4,'2017-01-04','07:00:00','Plant_BAR','10','2017-01-03 07:55:00','2017-01-03 08:53:00'),
(5,'2017-01-04','07:00:00','Plant_BAzz','50','2017-01-03 13:04:00','2017-01-07 06:22:00'),
(6,'2017-01-04','08:00:00','Plant_BAR','10','2017-01-03 05:00:00','2017-01-03 06:22:00'),
(7,'2017-01-04','07:00:00','Plant_BAzz','55','2017-01-03 05:00:00','2017-01-03 06:22:00'),
(8,'2017-01-04','07:00:00','Plant_BAR','0','2017-01-03 10:00:00','2017-01-03 06:22:00');

我需要一个时间点的资源，交货日期，小时结束，这取决于提交时间和有效停止有效率。当同一资源的子限制时间、结束时间和交货日期相同时，我遇到了问题。
返回max（submit\u time）行，如果有两个相同的提交时间，则返回max（eff\u stop）行
我有：

SELECT a.PK, a.Delivery_Date, a.Hour_Ending, a.Resource, a.Rate, a.Submit_Time, a.Eff_Stop  
FROM foo as a
INNER JOIN (SELECT Resource, Delivery_Date, Hour_Ending, max(Submit_Time) as max_submit
        FROM foo
        WHERE (Submit_Time < cast(Delivery_Date as datetime)-interval 1 day + interval 7 hour)
                AND (Delivery_Date ='2017-01-04') and (Hour_Ending ='07:00:00')
        GROUP BY Resource, Delivery_Date, Hour_Ending
) as b
ON a.Resource = b.Resource and a.Delivery_Date = b.Delivery_Date and a.Hour_Ending = b.Hour_Ending and a.Submit_Time = b.max_submit
WHERE (a.Delivery_Date ='2017-01-04') and (a.Hour_Ending ='07:00:00')
GROUP BY a.Hour_Ending, a.Delivery_Date, a.Resource;

这允许我为每个资源返回1行，这是最新的提交时间，但当资源具有相同的提交时间（在本例中为'2017-01-03 06:00:00'）时，我希望选择最大值（eff\u stop）的一行。
结果是：

PK  Delivery_Date   Hour_Ending Resource    Rate    Submit_Time Eff_stop
2   2017-01-04  07:00:00    Plant_BAR   9   2017-01-03T06:00:00Z    2017-01-03T06:55:00Z
7   2017-01-04  07:00:00    Plant_BAzz  55  2017-01-03T05:00:00Z    2017-01-03T06:22:00Z

我想要：

PK  Delivery_Date   Hour_Ending Resource    Rate    Submit_Time Eff_stop
3   2017-01-04  07:00:00    Plant_BAR   10  2017-01-03T06:00:00Z    2017-01-03T08:22:00Z
7   2017-01-04  07:00:00    Plant_BAzz  55  2017-01-03T05:00:00Z    2017-01-03T06:22:00Z

http://sqlfiddle.com/#!9/5断路器999/1/0
我试过一个左连接和右连接，两个内部连接，还有一堆其他的垃圾都不起作用。
任何帮助都将不胜感激！