gotha gotha - 4 months ago 20
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

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.