gotha gotha - 1 year ago 75
MySQL Question

MySQL group/order behaves differently in 5.7

I have a table that looks like this:

id | text | language_id | other_id | dateCreated
1 | something | 1 | 5 | 2015-01-02
2 | something | 1 | 5 | 2015-01-01
3 | something | 2 | 5 | 2015-01-01
4 | something | 2 | 6 | 2015-01-01


and I want to get all latest rows for each language_id that have other_id 5.

my query looks like this

SELECT * (
SELECT *
FROM tbl
WHERE other_id = 5
ORDER BY dateCreated DESC
) AS r
GROUP BY r.language_id


With MySQL 5.6 I get 2 rows with ID 1 and 3, which is what I want.

With MySQL 5.7.10 I get 2 rows with IDs 2 and 3 and it seems to me that the ORDER BY in the subquery is ignored.

Any ideas what might be the problem ?

Answer Source

You should go with the query below:

SELECT 
*
FROM tbl
INNER JOIN 
(

    SELECT 
     other_id,
     language_id,
     MAX(dateCreated) max_date_created
    FROM tbl
    WHERE other_id = 5 
    GROUP BY language_id
) AS t 
ON tbl.language_id = t.language_id AND tbl.other_id = t.other_id AND 
   tbl.dateCreated = t.max_date_created

Using GROUP BY without aggregate function will pick row in arbitrary order. You should not rely on what's row is returned by the GROUP BY. MySQL doesn't ensure this.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download