MySQL order results by date after a group by

Question

We are working on a project for school and have a small issue with a query. What we try to do is the following:

Select the education-unit(s) with the same version_vid and after that select the education-unit with the latest version_date. But whatever we try, the education-unit with the lowest euid is returning.

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

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

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

Thanks in advance!

Not sure about Yii but is it what you are looking at http://sqlfiddle.com/#!9/9929d/26 ? — Abhik Chakraborty, May 20 '15 at 12:20
Fiddle's are great. Kind of meaningless without a desired result, donchathink? — Strawberry, May 20 '15 at 12:31

score 0 · Answer 1 · edited May 21 '15 at 08:07

0

Maybe this can help you:

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

edited May 21 '15 at 08:07

arogachev

33,150
7
114
117

answered May 21 '15 at 07:59

Aditya Dharma

708
2
8
22

It works if I give the subquery an alias like this: `EducationUnit::find()->from('(SELECT * FROM education_unit ORDER BY version_date DESC) a')->groupBy('a.version_vid')` But this is the same solution as @Daan I think? Only in a Yii2 way. – Jelmer Visser May 21 '15 at 09:03

score -1 · Answer 2 · answered May 20 '15 at 12:22

-1

Wrap a select around your query, after that do the group by:

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

Why it didn't work in your query is because SQL has an execution plan as below:

FROM clause
WHERE clause
GROUP BY clause
HAVING clause
SELECT clause
ORDER BY clause

answered May 20 '15 at 12:22

Daan

12,099
6
34
51

2

While that probably will work.. it is not guaranteed to. The non-aggregate columns are assigned arbitrary values from the group, not necessarily the first one that is hit. I would avoid this solution. – Arth May 20 '15 at 12:29

score -1 · Answer 3 · edited May 23 '17 at 11:43

The reason you are getting the lowesteuid is that the ORDER BY is applied after the GROUP BY and the GROUP BY is choosing arbitrary values from the group to return.

This is a classic top in group question and has been answered previously

I personally like the answer supplied 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

That link is rather dated. The top answer works, but is orders of magnitude slower that a non-correlated subquery. (I didn't dv btw) — Strawberry, May 20 '15 at 12:30

MySQL order results by date after a group by

3 Answers3