Bug #5451 GROUP BY DESC not working
Submitted: 7 Sep 2004 15:22 Modified: 13 Sep 2004 11:05
Reporter: jason Engel Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server: MyISAM storage engine Severity:S1 (Critical)
Version:4.0.16-nt OS:Windows (Win 2k sp4)
Assigned to: CPU Architecture:Any

[7 Sep 2004 15:22] jason Engel
THE DESC modifier is not working. This was a previously reported bug that was meant to of being fixed. But does not seem to be fixed in the latest production version of MySql running on Win 2k.

How to repeat:

CREATE TABLE `tbl_news` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `link` bigint(20) NOT NULL default '0',
  PRIMARY KEY  (`id`),

INSERT INTO tbl_news VALUES("1", "1094566877");
INSERT INTO tbl_news VALUES("2, "1094566877");


SELECT id FROM tbl_news group by link DESC

Should return 2, but returns 1

Suggested fix:
Implement previously implemented patch onto current version for MySql running on win2k
[7 Sep 2004 19:41] Hartmut Holzgraefe
I don't know which other bug you are refering to, but
i can't see any fault in the result as you didn't have 'id' in the GROUP BY clause.
This is not standard SQL but MySQL allows this (unless you run in ANSI mode)
and behaves as documented in

  MySQL extends the use of GROUP BY so that you can use columns or 
  calculations in the SELECT list that don't appear in the GROUP BY clause. 
  This stands for any possible value for this group. You can use this to get 
  better performance by avoiding sorting and grouping on unnecessary items. 
  For example, you don't need to group on customer.name in the following query:

  mysql> SELECT order.custid, customer.name, MAX(payments)
      ->        FROM order,customer
      ->        WHERE order.custid = customer.custid
      ->        GROUP BY order.custid;

  In standard SQL, you would have to add customer.name to the GROUP BY
  clause. In MySQL, the name is redundant if you don't run in ANSI mode.

  Do not use this feature if the columns you omit from the GROUP BY part 
  are not unique in the group! You will get unpredictable results.

As your 'id' values obviously differ for a single 'link' value you get just that:
unpredictable 'id' values ordered desc by 'link' (although it is hard to see a 
sorting order for 'link' if there is only one value).
[7 Sep 2004 19:42] Hartmut Holzgraefe
Forgot to insert the documentation URL i pasted from in my
previous reply, it is 

[8 Sep 2004 11:14] jason Engel
Okay, i've thought of the problem some more. What I have is:

TABLE2 --------------
   id : link : title
1) 1 : alpha : title1
2) 2 : alpha : title2
3) 3 : alpha : title3
4) 4 : alpha : title4

select max(id), title from table2 group by link


 max(id) : title
1)      4 : title1

WHY DOES IT NOT RETURN (pulls hair out!)

 max(id) : title
1)      4 : title4

from reading the documentation i thought

select id, title from table2 group by link DESC

should return

    id     : title
1) 4     : title4

but returns
    id    : title
1) 1     : title1
[13 Sep 2004 11:05] Hartmut Holzgraefe
As stated above (docuemtation quote):

  Do not use this feature if the columns you omit from the GROUP BY part 
  are not unique in the group! You will get unpredictable results.

This applies here as your 'title' column values are not unique for the 
link='alpha' group.
[9 May 2007 16:06] Joe McDermott
I am still looking for a solution to this exact problem. My specific problem with screenshots can be seen here: http://forum.nucleuscms.org/viewtopic.php?p=76562#76562

I am having trouble getting distinct results from multiple tables, as GROUP BY always groups my results backwards, returning the oldest result available.

What is the correct way to get my desired result.

Thank you.