MySQL ORDER BY with GROUP BY

Question

I have problem with this order by cdate when I use group by, the order is not the last creation date, but I think random

SELECT vedio_files.ID vedio_files.mCat, vedio_files.sCat,vedio_files.smalltitle, vcategories.Name,vcategories2.Name as subcat
FROM vedio_files
INNER JOIN  `vcategories` ON vedio_files.mCat = vcategories.ID 
LEFT JOIN vcategories vcategories2 ON vedio_files.sCat = vcategories2.ID
GROUP BY  vedio_files.sCat   ORDER BY vedio_files.cdate DESC LIMIT 10

@Strawberry It's obvious -- that would cause a syntax error, but he's getting results (just not in the desired order). — Barmar, May 25 '18 at 15:23
People make copying errors all the time when posting here, you know that. — Barmar, May 25 '18 at 15:24
@Barmar The OP is welcome to correct the error. What's the problem? — Strawberry, May 25 '18 at 15:26
Nothing -- just let him know that he made a typo when copying, no need to be passive-aggressive about it. — Barmar, May 25 '18 at 15:27

spencer7593 · Accepted Answer · 2018-05-25T15:01:37.220

Correct.

The ORDER BY is applied last. That operation is after the GROUP BY collapses the rows.

The value of cdate on the row is indeterminate. The value is from some row in the group, but it's not necessarily the lowest one.

MySQL allows the query to run because of a non-standard extension to GROUP BY. (Other relational databases will throw errors about "non-aggregate expression in SELECT list not in GROUP BY". We can get MySQL to throw an error if we include ONLY_FULL_GROUP_BY in sql_mode.

The behavior documented here: https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

We can use an inline view to the minimum cdate for each sCat in vedio_files, and the join the results from the inline view back to vedio_files to get the rows with matching sCat and cdate.

Something like this:

SELECT vf.ID
     , vf.mCat
     , vf.sCat
     , vf.smalltitle
     , vc.Name
     , c2.Name         AS subcat
  FROM ( SELECT cd.sCat
              , MIN(cd.cdate) AS min_cdate
           FROM vedio_files cd
          GROUP
             BY cd.sCat
          ORDER
             BY MIN(cd.cdate) DESC
          LIMIT 10  
       ) mn
  JOIN vedio_files vf
    ON vf.sCat  = mn.sCat
   AND vf.cdate = mn.min_cdate  
  JOIN vcategories vc 
    ON vf.mCat = vc.ID 
  LEFT
  JOIN vcategories c2 
    ON c2.ID = vf.sCat
 ORDER
    BY ...

If there is more than one row in vedio_files with the same minimum date for sCat, this query return all of those rows.

MySQL ORDER BY with GROUP BY

1 Answers1