Using LIMIT within GROUP BY to get N results per group twice

Question

I have seen many "N values in GROUP BY," but I need it twice. I have:

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

I need a point in time effective Rate by Resource, Delivery_Date, Hour_Ending which depends on Submit_Time and Eff_Stop. I am running into issues when the Subimit_Time is identical for the same Resource, Hour_Ending, and Delivery_Date.

I want the max(Submit_Time) row returned, and if there is two identical submit times, I want the row with the max(Eff_Stop)

I have:

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;

This lets me return 1 row per Resource which is the most current Submit_Time, but when a Resource has and identical Submit_Time (in this case '2017-01-03 06:00:00') I want to then pick the one with the max(Eff_Stop).

The result is:

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

I want:

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/5cb999/1/0

I have tried a left and right join, two inner joins, and a bunch of other garbage that doesn't work.

Any help would be greatly appreciated!

What is your MySQL server version ? Do you have access to version 8.0.2 and above ? — Madhur Bhaiya, Nov 05 '18 at 19:25
We are running on an AWS RDS instance with MySQL 5.6.39. When I go to "Modify" it only lets me select up to 5.7.23 — user3511424, Nov 05 '18 at 20:06
If you use a proper `GROUP BY`, you won't have this problem. Also, what's the point of `GROUP BY` without aggregation? — Eric, Nov 05 '18 at 20:25
Here's a tutorial how to use `GROUP BY` properly. https://www.techonthenet.com/sql/group_by.php — Eric, Nov 05 '18 at 20:29
Thank you for your suggestion. I am still not there. I tried to add max(a.Eff_Stop) instead of a.Eff_Stop and it changed the column, but not the rate associated with the max Eff_Stop after getting the right Submit_Time — user3511424, Nov 05 '18 at 21:02
Possible duplicate of [Strange duplicate behavior from GROUP\_CONCAT of two LEFT JOINs of GROUP\_BYs](https://stackoverflow.com/questions/45250646/strange-duplicate-behavior-from-group-concat-of-two-left-joins-of-group-bys) — philipxy, Nov 06 '18 at 01:15
Likely this is a common error where what is wanted is the join of separate (join+)aggregations. (Eg see the one answer currently below.) Standard debugging says, look at every partial result & find the maximum correct code & the minimal problematic addition to it. But even now you would likely find this faq if you googled many clear, concise, precise characterizations of your problem. See also [this](https://dba.stackexchange.com/a/87479/439322). PS Always set ONLY_FULL_GROUP_BY. (Default ~5.7+.) PS Please clarify via post edits, not comments. — philipxy, Nov 06 '18 at 01:33

score 0 · Answer 1 · answered Nov 05 '18 at 22:42

I think this works:

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 PK, Resource, Delivery_Date, Hour_Ending, Rate, Eff_stop, 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
INNER JOIN(SELECT PK, Resource, Delivery_Date, Hour_Ending, Rate, Eff_stop, Submit_Time, max(Eff_Stop) as max_stop
        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, Submit_Time
) as c
ON a.Resource = c.Resource and c.Delivery_Date = a.Delivery_Date and a.Hour_Ending = c.Hour_Ending and a.Eff_Stop = c.max_stop
WHERE (a.Delivery_Date ='2017-01-04') and (a.Hour_Ending ='07:00:00');

Using LIMIT within GROUP BY to get N results per group twice

1 Answers1