MYSQL ORDER BY Being Ignored When GROUP BY Applied On Result

Question

I have a table of bills where there can be multiple entries for year+month, I need to select only one of each of the year+month pairs. Specifically the pairs with the highest id field, eg the newest entries in the table and crop out older duplicates.

Here is an example query and result, I've added month=1 or 2 to limit size.

SELECT id, year, month, cost_estimate
FROM tblbillforecast
WHERE type = 'E' AND buildingid = 19 AND (month = 1 OR month = 2)
ORDER BY id DESC;

Results:

Desired results:

SELECT * FROM
(SELECT id,year,month,cost_estimate FROM tblbillforecast WHERE type = 'E' and buildingid=19 and (month=1 or month=2) order by id desc) as b
GROUP BY b.month,b.year

The above query works except it is ignoring the order by id desc that should happen first. I'm not sure what is happening under the hood or if there is a better way to accomplish this but I'm stuck. I've tried more than 10 solutions from other SO similar answers but they don't work for my scenario.

ORDER BY applied in the subquery is **always** ignored in outer query. If this ORDER BY does not accompanied with LIMIT then it makes no sense and can be removed. — Akina, Jan 20 '22 at 05:08
Your GROUP BY is uncomplete. All columns which are not a part of grouping expression or an argument of aggregate function receives indefinite values randomly selected from all existing ones for a group. Study ONLY_FULL_GROUP_BY. — Akina, Jan 20 '22 at 05:10
Maybe you need this: [Retrieving the last record in each group - MySQL](https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql)? — astentx, Jan 20 '22 at 05:22
@Akina if I add limit 1 it limits the whole query not just the matching pairs year+month. — Steve Seeger, Jan 20 '22 at 05:23
@astenx Thanks, I have tried the three highest ranked solutions on that post with no luck. — Steve Seeger, Jan 20 '22 at 05:24
*if I add limit 1* Why do you think that LIMIT 1 may help? it changes the query logic, and no guarantee that new logic matches needed one. — Akina, Jan 20 '22 at 05:25
"With no luck" tells exactly nothing about the real issue. Either your description of the task os incomplete, or the code is wrong. Please post your code for the applied answer — astentx, Jan 20 '22 at 05:27
what are you grouping your results for? don't see any aggregates happening here like COUNT(), SUM() etc... Appears to me either you have framed your question incorrectly or the sample code provided is wrong... — GSM, Jan 20 '22 at 06:36
@GSM This may not be it's intended use, but I was trying to use it to limit each grouped result to the 'top 1' with highest id value to only select the newest row from each group. It works except it selects the oldest, and ignores the inner order by clause. — Steve Seeger, Jan 20 '22 at 15:08

score 0 · Answer 1 · answered Jan 20 '22 at 18:04

After revisiting a similar question's approved answer I found a way to make it work in my scenario and have acceptable performance:

SELECT m1.*
FROM (SELECT id, year,month,cost_estimate FROM tblbillforecast WHERE type = 'E' and buildingid=19) as m1 LEFT JOIN (SELECT id, year,month,cost_estimate FROM tblbillforecast WHERE type = 'E' and buildingid=19) as m2
 ON (m1.year = m2.year AND m1.month=m2.month AND m1.id < m2.id)
WHERE m2.id IS NULL;

Credit to approved answer here: Retrieving the last record in each group - MySQL

MYSQL ORDER BY Being Ignored When GROUP BY Applied On Result

1 Answers1