Clever Geek Handbook

MySQL order results by date after group

We are working on a project for a school and have a small problem with the request. We are trying to do the following:

Select education-unit(s)

with the same version_vid

and then select education-unit

with the latter version_date

. But whatever we try, it comes back education-unit

with the least euid

.

We are using Yii2 framework for this project, we are using ActiveQuery:

EducationUnit::find()->groupBy('version_vid')->orderBy('version_date DESC');

SQL Fiddle: http://sqlfiddle.com/#!9/9929d/2/0

Thanks in advance!

+3


source to share


3 answers


Maybe this can help you:



EducationUnit::find()
    ->from('select * from education_unit ORDER BY version_date DESC')
    ->groupBy('version_vid')
    ->all();
0


source


Wrap the selection around your query, then execute the group:

SELECT * FROM
( SELECT * FROM `education_unit` ORDER BY `version_date` DESC ) a
GROUP BY a.`version_vid`


Why didn't this work in your query, since SQL has an execution plan like below:

  • FROM clause
  • WHERE clause
  • GROUP BY clause
  • HAVING clause
  • SELECT clause
  • ORDER BY clause
-1


source


The reason you are getting the lowest euid

is because it ORDER BY

applies after GROUP BY

and GROUP BY

fetches arbitrary values ​​from the returned group.

This is a classic top in a group question and was previously

I personally like the answer provided by Bill Karwin, which when applied to your situation becomes:

   SELECT t.*
     FROM table t
LEFT JOIN table t2
       ON t2.version_vid = t.version_vid 
      AND t2.version_date > t.version_date
    WHERE t2.version_vid IS NULL

Another common solution is:

   SELECT t.*
     FROM table t
     JOIN (
       SELECT version_id, MAX(version_date) version_date
         FROM table
     GROUP BY version_id
          ) t2
       ON t2.version_id   = t1.version_id
      AND t2.version_date = t1.version_date
-1


source






More articles:


All Articles