MySQL Bugs: #4463: GROUP takes the first-found values

Bug #4463	GROUP takes the first-found values
Submitted:	8 Jul 2004 15:01	Modified:	4 May 2007 12:13
Reporter:	Mr. Venom	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S4 (Feature request)
Version:	<4.1	OS:	Any (doesn't matter)
Assigned to:		CPU Architecture:	Any
Triage:	D5 (Feature request)

Description:
For example, I have the usual article/comment structure. Now I want to show the last comment for the article by using just one query. Logically I would try to use the greatest value of the comment's PK in the ORDER clause:
SELECT * FROM t1 LEFT/RIGHT JOIN t2 ON t2.`parent`=t1.`id`
GROUP BY t2.`parent`
ORDER BY t2.`id` DESC

The problem is that GROUP BY executes first (which is OK),  but takes the first-found values with the parent.
In v>=4.1 I would make a subquery with the rows first ordered descending and then 'd group them.

An other way is to select all of the rows into a temporary table, descending and then do a second select with the grouping. Bu the table has about a million of rows, so I think this could be quite restraining for an everyday use.

The next I think of is to mark the newest comment as last by adding and updating a "flag" column, eg:

UPDATE t2 SET `islast`=NULL WHERE parent='(current article's ID)'
INSERT INTO t2 SET `islast`=1, other values

and appending the topmost JOIN query with a AND/WHERE islast=1

How to repeat:
H2R: see description.

Suggested fix:
Perhaps, add a GROUP switch/an other function which would start the grouping in an 'upscale' way - from the end of table up to the beginning.

http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html

"Do *not* use this feature if the columns you omit from the GROUP BY part are not constant in the group. The server is free to return any value from the group, so the results are indeterminate unless all values are the same."